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.

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

Gestalt Principles: Understanding Its Importance In Report Design

The aim of the Gestalt principles of design is to understand how humans typically gain meaningful...

Power Query: How To Quickly Add Notes

In this post, we’ll learn different ways to add notes in Power Query, which can benefit the people who...

How To Sort & Filter Chart In Report Builder

In this tutorial, you’ll learn how to sort and filter data in a chart in Report Builder. This tutorial...

Power BI Filtering Techniques For Tables

In this blog, we’re going to learn about some essential Power BI filtering techniques for tables. We'll...

Calculating Weekly Sales w/DAX In Power BI

Calculating the difference between weekly sales results in Power BI is unfortunately not that easy. You...

Three Ways To Use R Script In Power BI

How to Use R In Power BI There are three ways that R can talk to Power BI. This tutorial will teach you...

Creating Dynamic Ranking Tables Using RANKX In Power BI

In this tutorial, we’ll look at a unique technique inside Power BI that allows us to create dynamic...

Introduction To Creating An SSRS Bar Chart

Bar charts are a popular way of visualizing data and are often used to compare data across different...

Find Top Customers Using RANKX In Power BI

Power BI is an amazing analytical engine! In this blog post, I'm going to show you how you can find...

Power Query Editor: Staging Queries

In this tutorial, we'll learn about staging queries using Power Query editor. This is a new...

Running Totals In Power BI Using DAX

Sometimes calculating running totals in Power BI is a far better away to analyse trends than just...

Selenium IDE In Power Automate UI Flows

In this tutorial, we'll discuss the Selenium IDE option in Power Automate UI flows. When creating UI...