Power BI DAX Compute Latest Value for filtered rows

Question

I have data in a Power BI DirectQuery table that looks line this:

Name    Tx     TxDate   Other Columns
-------------------------------------------------------
A       1      1/1/2017
A       2      1/3/2017
A       3      1/4/2017
B       4      1/5/2017
B       5      1/6/2017
C       6      1/1/2017
C       7      1/2/2017
C       8      1/9/2017

In a table visual, I want to show only the latest rows for each name:

Name    Tx     TxDate   Other Columns
-------------------------------------------------------
A       3      1/4/2017
B       5      1/6/2017
C       8      1/9/2017

However, I am using a slicer to help filter our dates and I want the latest rows that are in the sliced data. For example, if the sliced date range is 1/1/2017 to 1/2/1017, inclusive, I want to show this:

Name    Tx     TxDate   Other Columns
-------------------------------------------------------
A       1      1/1/2017
C       7      1/2/2017

I've been trying to accomplish this by creating a measure with a DAX expression to compute the latest Date for each name, then adding a filter to only show those where LatestTxDate = TxDate, but it is not working:

LastTxDate = 
    MAXX(
        GROUPBY(
            Table,
            [Name],
            "Latest Tx Date", 
            MAXX(CURRENTGROUP(), Table[TxDate])
               ), 
        [Latest Tx Date]
         )

The idea being that I calculate a new measure and then only make the row visible if the TxDate = LastTxDate.

Name    Tx     TxDate     LastTxDate  Other Columns
-------------------------------------------------------
A       1      1/1/2017   1/1/2017
A       2      1/3/2017   1/1/2017
A       3      1/4/2017   1/1/2017
B       4      1/5/2017
B       5      1/6/2017
C       6      1/1/2017   1/2/2017
C       7      1/2/2017   1/2/2017
C       8      1/9/2017   1/2/2017

This feels embarrassingly simple, but everything that I have tried doesn't work. Any help is appreciated.

Solution

I had the same problem as you and spent a whole week trying to solve it, because I couldn't found any guide on the internet. So I thought I could lend you a hand even though this was asked 9 months ago.

First things first: you need to do this with measures, as they're the only thing that is dynamically calculated within a PowerBI report. If you use a calculated column or a M formula inside the Query Editor, it will only be calculated whenever you refresh your data. They won't be affected by slicers and filters inside your report.

After trying a lot of stuff, I came up with the idea of making a measure that checks if each row is the most recent row for each group, then the measure will take a value of '1'. If it's not the last row, it will take a value '0'.

This is how I managed to do that (note: it was NOT that simple):

1. Build a calendar table that ranges from the minimum to the maximum of your date column. This must be done because if you use the date column itself in the next steps, the data will behave strangely. This new table's column will be used as the date slicer within the report:

   Calendar = CALENDAR(MIN('Table'[TxDate]); MAX('Table'[TxDate]))
   

2. Now create a new measure in your data table that will filter the dates higher than the selected date in the report slicer and then it will find the latest date for each category or group:

   Check =
   VAR DateFilter = MAX('Calendar'[Date])
   VAR LastDate = CALCULATE(
       MAX('Table'[TxDate]);
       ALLEXCEPT(
           'Table';
           'Table'[Name]
       );
       'Table'[TxDate] <= DateFilter
   )
   RETURN IF(LASTNONBLANK('Table'[TxDate]; 1) = LastDate; 1; 0)
   

   If you build a table visual with all your 'Table' columns and LastDate as a measure, you will see that each row will have the most recent date of each group as a value for LastDate. Something like this:

   Name    Tx     TxDate     LastDate
   ---------------------------------------
   A       1      1/1/2017   1/4/2017
   A       2      1/3/2017   1/4/2017
   A       3      1/4/2017   1/4/2017
   B       4      1/5/2017   1/6/2017
   B       5      1/6/2017   1/6/2017
   C       6      1/1/2017   1/9/2017
   C       7      1/2/2017   1/9/2017
   C       8      1/9/2017   1/9/2017
   

   It will be affected by the date slicer if you have one in your report. So the behaviour is right up to this point. Then I use the formula LASTNONBLANK() to give the date column a row context, thus being able now to compare between TxDate and LastDate and if both are the same, Check will take the value '1'.

   Name    Tx     TxDate     LastDate   Check
   ---------------------------------------------
   A       1      1/1/2017   1/4/2017   0
   A       2      1/3/2017   1/4/2017   0
   A       3      1/4/2017   1/4/2017   1
   B       4      1/5/2017   1/6/2017   0
   B       5      1/6/2017   1/6/2017   1
   C       6      1/1/2017   1/9/2017   0
   C       7      1/2/2017   1/9/2017   0
   C       8      1/9/2017   1/9/2017   1
   

3. [Check] measure can't be used in a graph visual as of now, so we need to create another measure that will just count all rows in which [Check] takes the vale '1'. This is the simplest step as we just need a SUMX to sum all the rows of a filtered table:

   Count of Tx = SUMX(
       FILTER('Table'; [Check] = 1);
       [Check]
   )
   

Now you have the total count of all of your table rows with the most recent date within the range given by the date slicer. You can use this measure in a graph visual to show how many of those rows correspond to each category. For example, you are able to build a pie chart that shows how many "names" have the value "3" for Tx, how many have the value "5" and so on.

If you want a table visual that shows which is the latest date for each category, you can, instead of [Check], use [LastDate] as a measure like this:

    LastDate =
    VAR DateFilter = MAX('Calendar'[Date])
    RETURN CALCULATE(
        MAX('Table'[TxDate]);
        ALLEXCEPT(
            'Table';
            'Table'[Name]
        );
        'Table'[TxDate] <= DateFilter
    )

If you also want a measure that tells you which is the latest Tx for each category, you can change MAX('Table'[TxDate]) for MAX('Table'[Tx]). There are a lot of possibilities. My suggestion is that you study these formulas so you understand what they are doing in every step, so you can modify them for your needs. You can also combine the two measures and get a table visual like this one:

Name   LastTx   LastDate
--------------------------
A      3        1/4/2017
B      5        1/6/2017
C      8        1/9/2017

Hope this helps you even 9 months after you asked, or maybe help other people with the same problem as we had.

Comments

[Dan]

This doesn't do what I need it to do. It ignores the slicer. I added it as a measure and it just gives me the same value as the TxDate column. I cannot add it as a column because I am using DirectQuery - it gives and error. I need the max TxDate for ALL rows in the sliced range for the Name in question.

[Alexis Olson]

@Dan It should work just fine as long as you aggregate your Tx column as well. Otherwise, you'll have as many rows as Tx values.

[Damjan Tomic]

Thanks for this helpful reply! I have similar data model and have solved the issue with dates in a similar way, but I have a problem when adding the measure (Tx in your case) to a matrix with totals. Totals are not calculated correctly at all. Just wondering if you tried calculating the totals with your data model and if it worked?