# DAX MAXX Function In Power BI – Discover Last Date

I’m going to share a quick and efficient formula around the DAX MAXX function in Power BI. In this example calculation, I’ll show you how to find the last date something occurred using MAXX, which is an iterating function. You may watch the full video of this tutorial at the bottom of this blog.

I’ve seen a lot of people asking about this on the Enterprise DNA forum. A big part of it is just understanding what an iterating function enables you to do.

In this tutorial, I have a simple survey data, wherein the same customer/person can take a survey several times. And what we want to work out is when was the last date that someone actually took the survey.

I’ve done this in a calculated column (the Max Date in the table above), but I’ll show you how to do it in a measure. I personally think that it’s easier and much better in a measure.

This is just one scenario, one example of data. The same technique can be used in many ways and I want you to think more broadly around how this technique could be used.

Table of Contents

## Using DAX MAXX In A Measure

Looking at the formula below, the first thing to work out in this particular context is the variable, VAR CurrentName, which is the name of the person or customer.

Then, work through a virtual table, where we can look through every line of the survey data table, but filter that virtual table by just the survey results for the current name.

So, as you can see in the Name and Max Survey Date table, it’s showing the specific name of the person for each date. So we’re basically getting a virtual table of only the surveys that each person took.

Because it’s wrapped inside MAXX, it’s going to just return one result, the max result, which is going to be the Date Taken. So, we’re going to get one date, which just shows when a person took the survey last.

## Other Ways To Use The MAXX Formula Pattern

There are a lot of derivatives around this formula. For example, you wanted to find out the date that is seven days before the last time someone did something (such as purchase or pay, etc.). You could just add some metrics within the actual iterating function itself. So in this case, I added – 7. And you’ll see that the results changed to seven days before.

You could even take this farther. All you need to do is probably turn this MAXX formula into a variable. So you would get the Max Date, but then you would filter the virtual table, not only by name, but also make sure all the dates are before the Max Date. That would give you the Max Date of the date prior to the Max Date.

There are so many ways that you can utilize this technique to get some Max or even Min. You could flip this around and use MINX as well.

And what’s interesting here is that you can reuse or place this formula pattern into a different context to find many other insights. For example, if you look at the table below, which is a breakdown of every single time the people took the survey, but you can still see when the next survey date was.

And then from there, you could probably create another measure, which is Days From Last Taken. This way, you won’t need to do this in a calculated column.

## Conclusion

The examples that I’ve demonstrated in this tutorial show you why you don’t need to create calculated columns. You can do this all within a measure very easily. I hope that you can see how the DAX MAXX formula pattern works and that you can implement it in your own business analysis and reports.

If you want to know more about this Power BI DAX MAXX function or how iterating functions work, check out the links below and my other tutorial videos on this topic. Using these functions is an absolutely crucial concept to use in power BI, especially when using DAX formulas.

Cheers!

Sam

[youtube https://www.youtube.com/watch?v=7PlIFVdu_XQ?rel=0&w=784&h=441]

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Power BI Report Examples And Best Practices – Part 1

In today's post, I'd like to present some Power BI report examples and best practices. In my own Power...

## Power BI Python Tutorial: How To Translate Texts

This blog will demonstrate how to perform language or text translation using Python and pipe it over...

## Measure In Power BI: Optimization Tips And Techniques

In this tutorial, you’ll learn how to optimize a measure in Power BI. Optimizing measures in your...

## How To Use Power Query Row And Column Selection

This tutorial will discuss how to use selection and projection inside the Power Query Editor. Selection...

## Use Tabular Editor To Create Calculation Groups In Power BI

Today's blog post will give you an introduction to calculation groups. I'll try to answer four basic...

## Effective Data Storytelling: Asking The Right Questions

To ensure that we have a good story to tell, effective data storytelling by asking the right questions...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Turning Calendar Type Layout Into Tabular Format In Power BI Using Query Editor

Analyzing the data that we have can be difficult if it is not correctly arranged. In this tutorial, I...

## Create A Lookup Table From Subtotals: Optimization In Power BI

In this tutorial, we’ll go through a technique that will create a lookup table from subtotals using the...

## Showing Difference Between Sales And Budgets To Date – Forecasting In Power BI

The topic that I'm going to discuss in this tutorial was part of a full-hour workshop on budgeting and...

## How To Merge Queries In Power BI

Merging is another powerful transformation to optimize our...

## Unpacking The Evolution Of Power BI – From Launch to Now

Microsoft Power BI is a dynamic business intelligence platform that has revolutionized the way...