PowerBI DAX for Finding Last Record

Question

Question

What is an efficient way to create a calculated column finding the last value of my DATE column, using the ModifiedOn column, per ID? I don't want the MAX date, just the last record (even if the last record is the minimum). Also, my table is a calculated column.

Example Table

ID DATE ModifiedOn

A 2/4/2020 1/16/2019

A 2/5/2020 1/17/2019

B 3/2/2020 2/7/2020

B 3/3/2020 2/8/2020

B 3/1/2020 2/9/2020

Current Formula

LastRecord = 
VAR Max_Date =
CALCULATE (
    MAX ( 'Table1'[ModifiedOn] ),
    ALLEXCEPT ( 'Table1', 'Table1'[ID] )
 )

RETURN
IF (
    Table1[ModifiedOn] = Max_Date,
    Table1[DATE]
    )

Current Results

But using the formula I get a calculated column that looks like this: I keep getting blanks where they should be filled with the LAST recorded date of that ID.

Answer 1

Use the following dax formula to create the expected column:

Column = 
VAR __id = 'Table'[ID]
VAR __lastMod = 
    CALCULATE( 
        MAX( 'Table'[ModifiedOn] ),
        FILTER( 'Table', 'Table'[ID] = __id )
    )

VAR __lastDate =
    CALCULATE(
        MAX( 'Table'[Date] ),
        FILTER( 'Table', 'Table'[ID] = __id && 'Table'[ModifiedOn] = __lastMod )
    )

Return __lastDate