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.
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.
***** Related Links *****
Using The MAXX DAX Function In Power BI To Calculate Recent Values In Your Data
Last Purchase Date in Power BI: When Did Your Customers Make Their Last Purchase?
Working With Iterating Functions In DAX
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
