calculating difference between current to previous most recent result per group

[Anonymous]

I have a table that looks similar to

control	result	date
1	12	20-9-2019
1	15	22-9-2019
1	9	23-9-2019
2	10	20-9-2019
2	5	22-9-2019
2	23	23-9-2019
3	8	20-9-2019
3	34	22-9-2019
3	30	23-9-2019

I want to create a calculated new column that shows the difference between the result on that row and the result on the closest previous date per control (results come in with irregular intervals)

I want it to end up looking like

control	result	date	difference
1	12	20-9-2019
1	15	22-9-2019	3
1	9	23-9-2019	-6
2	10	20-9-2019
2	5	22-9-2019	-5
2	23	23-9-2019	18
3	8	20-9-2019
3	34	22-9-2019	26
3	30	23-9-2019	-4

How do i do this?

[Icey · Accepted solution]

Hi @Anonymous ,

Refering to the post @amitchandak provide, I create these columns:

Index =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[control] ),
        'Table'[date] < EARLIER ( 'Table'[date] )
    )
) + 1

Value Change =
'Table'[result]
    - CALCULATE (
        SUM ( 'Table'[result] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[control] ),
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Refer

https://community.powerbi.com/t5/Desktop/Calculate-Difference-Between-Latest-Date-and-Previous-Date/...