# Using The MAXX DAX Function In Power BI To Calculate Recent Values In Your Data

This tutorial will talk about a common scenario that’s frequently asked in the Enterprise DNA support forum. You’re going to learn how to work out the most recent or the last value in your data using the MAXX and FILTER DAX functions in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

For this example, there’s a list of data and within that list the customer is referenced multiple times. So, you want to work out the recent value for that particular customer name.

The example presented in the forums was in the form of a survey data. You want to work out the most recent or the last time that a particular customer took the survey.

There are a couple of ways to solve this. You can perform the solution using calculated columns or measures.

Using a Calculated Column gives more aesthetic while using a Measure allows your models to be more dynamic.

Here’s how to work out the most recent date for any dimension in your data set using two solutions.

## Solution #1: Using Calculated Columns

Here’s how to solve the problem using Calculated Columns.

### 1. Create A New Column

The first step is to create a new column in the table that was given as an example in the forums. This new column will contain the Max Date.

This is the logic used on the formula for the Max Date:

Whenever you encounter problems in Power BI that require the maximum date or number, the MAXX DAX function is the best one to use.

This function is the key in creating the solution for this example.

### 2. Create A Variable

Now, you need to create a new variable using the VAR function. In this example, the new variable is called CurrentName.

For each different row, you want to work out the name of each person. This part is quite easy since you simply need to reference the column name in a Calculated Column.

At every single row, you only want to isolate the people with the same name as the current row.

Looking at the table, you can see that it has three different names with two entries for each name.

You need to isolate those names and then work out the maximum date that a particular person has transacted with you. The transaction could be in terms of completing a purchase or survey.

### 3. Create A FILTER Statement

To isolate the names, you first need to use the FILTER and ALL function.

This will remove all the filters within the table. This will also iterate through every single row regardless of context.

Then, you need to work out if the Name in the Survey Data is equal to the CurrentName variable.

## 4. Use The MAXX Function

Now, the MAXX DAX function is going to get a table containing only two rows. This is the result of filtering and iterating through each row to find out if the name is equal to the current name. In this case, the current name is John Smith.

You then need to evaluate the max. The MAXX DAX function calculates the max for you. So, all you need to do is put the column in the formula:

When you change the Max Date column’s format into a date, you’ll see that the max date for John Smith is February 23.

The max date for Nancy Jones is September 25, while it’s September 22 for Elaine Anderson.

This is a very simple technique and its logic can be applied to different scenarios. You can use this to find out the last purchase or sale amount.

## Solution #2: Using A Measure

Here’s how to solve this problem using Measures.

Using measures allows you to work out the last date and then branch out into other calculations.

This is the best way to run any calculations in Power BI.

### 1. Create A Table

First, you need to create a table.

This table only contains two columns. The information contained in the other columns are irrelevant for this calculation.

### 2. Create A New Measure

You then need to create a new measure.

This measure has the same formula that was used in the calculated columns solution. However, you need to change the Survey table to Survey Data.

And instead of simply referencing the column name, you need to use the SELECTEDVALUE function.

This captures what context you’re in.

SELECTEDVALUE enables you to grab the text value from its context.

This technique is also very dynamic compared to other calculations since you can take out the date column and only retain the Max Date for each customer.

## Conclusion

Solutions don’t have to be difficult. They can be done using simple ways.

For this example, you can either go for the Calculated Columns solution, or use Measures.

Hopefully, you understood the concept of how to manipulate the virtual table within the MAXX DAX function.

You can also use the FILTER function to iterate through information and then adjust the virtual table based on the parameter you put into the FILTER statement. MAXX will then do the work from here.

All the best,

Sam

***** Related Course Modules *****
Time Intelligence Calculations
Unique Analytical Scenarios
Mastering DAX Calculations

## 8 comments on “Using The MAXX DAX Function In Power BI To Calculate Recent Values In Your Data”

1. stephanie falvay says:

Very good explanation. But if I prefer that the column shows q1 instead of the date, how do I do? I want the value of q1 to be reflected in the column at its maximum date

For example, in Nancy Jones the maximum date is September 25, and I want the new column to give me the heat of q1 = Neither agree or disagree

Thanks

1. Hello stephanie falvay, thank you for posting your query onto our blog post.

Well, it’s always a little bit difficult to judge and provide the results without looking at the working of the PBIX file.

We encourage you to please write back to us onto our Community Forum by providing the PBIX file along with the problem that you’re facing as well as well the results that you’re trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.

Hoping this helps you.

Happy Learning!!!

https://forum.enterprisedna.co/

2. Henrik Waeltken says:

This works great when this measure is deployed in a table where the Name is present and SELECTEDVALUE returns context. How might we create this within a measure that does not occur against this context? I’m trying to filter an effective dated records table down to only the latest record per user and then use that table in CALCULATE(COUNTROWS(. This measure will not be deployed in visuals or tables which list the names or users so the SELECTEDVALUE will not provide a single value or will return null.

1. Hi Henrik Waeltken – Thanks for your interest in Enterprise DNA Blogs.

It seems your requirement is to get count of distinct values of the column from your table. For this you can use functions like ALL, Distinct, Values encapsulated by CountRows.

If your requirements is to get latest date for each customer then best option will be to use Summarize or GroupBy function in the measure. It will be like SUMMARIZE (Table Name, ColumnName,min(Dates)).

If still you are facing issues with your query, then raise a Help ticket at EDNA forum forum.enterprisedna.co

Please feel free to ask any other query related to this Blog Post.

3. core2plus says:

hi, how i can calculate differences between previous year last value and current year last value, the scenario is a product purchased by company multiple times every year and want to check the difference between price for 5 years, get the last purchase rate of previous year and get last purchase rate of current year, apply formula as ” current rate – previous rate “. the last dates of purchase can vary even by months, in 2013 last purchase was in Aug and in 2014 last purchase was in Nov. and so on… can you help us please. regards

1. Hi core2plus – Thanks for your interest in Enterprise DNA Blogs.

For getting the current and previous year MAX value we can use same Technique provided by adding a Filter for YEAR from Date/Fact table inside the Filter condition. To get Max Year we can use below expression in a variable.

CALCULATE(max(Dates[Year]),all(Dates))

It will require creating either two separate measures for Current and previous year value and can also be done in a single measure by using variables.

Hope this is helpful. If not clear or not able to make it execute, do raise a request at EDNA forum https://forum.enterprisedna.co/ and one of our Experts/Member will help you.

Please feel free to ask any other query related to this Blog Post.

This site uses Akismet to reduce spam. Learn how your comment data is processed.