The LASTDATE function in DAX retrieves the last date in the filter context. In this puzzle, you have to fix a wrong measure where LASTDATE is not working as expected.

Scenario

The data model has a simple Accounts table with 8 rows (one for the last available date in each quarter) and 3 columns: Account, Date, and Balance.

LASTDATE-table

The data model does not contain a Date table, even if an additional Month column is automatically created by Excel 2016 and Power BI Desktop.

The Challenge

The data model has a measure that should display the sum of the last available date in the period selected in a report. This is the definition of such a measure:

Last Balance :=
CALCULATE ( 
    SUM ( Accounts[Balance] ),
    FILTER ( ALL ( Accounts ), Accounts[Date] = LASTDATE ( Accounts[Date] ) ),
    VALUES ( Accounts[Account] )
)

However, using this measure the result of the following report is wrong.

LASTDATE-wrongresult

As you see, the Last Balance measure display a wrong result, which is the sum of all the dates for each account, regardless of the filter over date.
The expected result is the following one, where you see that the totals at the year level and at the account level correspond to the month of December:
LASTDATE-correctresult

You have to fix the formula by only replacing one function name, without modifying the structure of the measure.

Hints

You can obtain the right result in many different ways. It is ok to try different approaches, but before looking at the solution, try to understand the exact reason why the current measure does not work, and what is the single change you have to apply to make this formula working, without altering its original design.

The zip file for this puzzle contains examples in both Power BI and Excel.

Solution

To see the solution, just press the button below.

View solution