Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

calculating difference between current to previous most recent result per group

I have a table that looks similar to

 

controlresultdate
11220-9-2019
11522-9-2019
1923-9-2019
21020-9-2019
2522-9-2019
22323-9-2019
3820-9-2019
33422-9-2019
33023-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

 

controlresultdatedifference
11220-9-2019 
11522-9-20193
1923-9-2019-6
21020-9-2019 
2522-9-2019-5
22323-9-201918
3820-9-2019 
33422-9-201926
33023-9-2019-4

 

How do i do this?

 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

     

     

2 Replies