Last Date - SQLBI

The correct solution is using MAX instead of LASTDATE:

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

The LASTDATE function internally executes a context transition. You can think that the syntax

LASTDATE ( table[column] )

Internally corresponds to:

CALCULATETABLE ( 
    FILTER ( 
        ALL ( table[column] ), 
        table[column] = MAX ( table[column] ) 
    ) 
)

For this reason, calling LASTDATE when you have a row context (as it is the case in the FILTER of the original Last Balance formula) generates a context transition, and the internal MAX function only consider the date of the current row in the accounts table during the FILTER in Last Balance.

Remember: LASTDATE is a table function, which is useful to use in a filter argument of CALCULATE, whereas MAX is a scalar function, which cannot be used in a filter argument. For this reason, you can write:

CALCULATE ( <expression>, LASTDATE ( table[column] ) )

But you cannot write this:

CALCULATE ( <expression>, MAX ( table[column] ) )

You have to write:

CALCULATE ( <expression>, table[column] = MAX ( table[column] ) )

Which corresponds to:

CALCULATE ( 
    <expression>, 
    FILTER ( 
        ALL ( table[column] ), 
        table[column] = MAX ( table[column] ) 
    ) 
)

Download