Date difference with selected event id and group by ids

[pprakash]

Hi i have a table. i want to calculate date diff . i want to group indent id and calculate date difference with selected event indent id. 

for example group indent id 85. there are 4 indent_event_id ie 5,1,2,10, i want the date diff ie created_at between indent_event_id 2 and 1, then ndent_event_id 5 and 2 and then ndent_event_id 10 and 5. 

Please help

[v-jiascu-msft · Accepted solution]

Hi @pprakash,

Please download a demo from the attachment. I assumed the ID is unique.

Column =
VAR currentId = [id]
VAR lastID =
    CALCULATE (
        MAX ( Table1[id] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[indent_id] ), Table1[id] < currentId )
    )
VAR lastCreateAt =
    CALCULATE (
        MAX ( Table1[created_at] ),
        FILTER ( 'Table1', Table1[id] = lastID )
    )
RETURN
    DATEDIFF ( lastCreateAt, [created_at], DAY )

Best Regards,