DAX - Return Immediate Previous row value based on existing Rank

[Anonymous]

Hello there, 

I need a calculated column "Result" to be filled up with the immediate previous row value as shown below.

Any suggestions please? PS: Do not want this to be a measure value, so may be this could be returned as a table output which can act like a temporary table. There are additional calculations lined up on top of this output.

ID	Date	Rank	Result ( Immediate Previous Date based on Rank for each ID)
1	01/01/2021	2
1	02/01/2021	1	01/01/2021
2	01/01/2021	3
2	03/01/2021	2	01/01/2021
2	05/01/2021	1	03/01/2021

Thanks,

Pravallika

[Fowmy · Accepted solution]

@Anonymous 

Add the following calculated column to your table:

Result = 
var __currentrank = Table11[Rank]
var __currentid = Table11[ID]
var result  = 
    MAXX(
        FILTER( Table11 , Table11[ID] = __currentid && Table11[Rank] = __currentrank + 1 ),
        Table11[Date]
    )
return
    result