# 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 Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...

## Format Data In Power BI: Addressing Irregular Data Formats

In today's blog post, we'll discuss Problem of the Week #6. I'll show you how to format data in Power...

## Calculating A Rolling Average In Power BI Using DAX

In this tutorial, I’m going to show you how to calculate a dynamic Rolling Average in Power BI using...

## Paginated Report In Power BI: An Introduction

In this tutorial, you’ll learn about paginated reports in Power BI. A paginated report is another name...

## Creating Measure Tables & Subfolders In Power BI

For today's blog, I'll cover measure tables and subfolders. We're going to set up measure tables, and...

## Ultimate Guide To Multiple IF Statements In Power BI

If you are looking to create more complex logic, then using multiple if statements in Power BI is a...

## Highlight Highest & Lowest Values Using Quick Measure In Power BI

Quick Measures Pro is a powerful external tool to streamline data analysis and visualization in Power...

## Power BI Desktop Update: The Charticulator Visual

Today, I would like to go over something that I found out by scrolling through Twitter. What I have...

## How To Build a Portfolio And Showcase Your Data Projects to Employers

In today’s data-driven world, the ability to work with data has become an essential skill. Whether...

## New vs Existing Customers – Advanced Analytics In Power BI

If you’re an online retailer or a high frequency sales operation, then understanding your customer...