DAX - find difference from first and last

[JamesH245]

Hi, I have data structured like this

 

What I'm trying to do is get an output row like this:

id         Trend

324       .5

This takes the first score and last score from an ID, filtered by time, and finds the difference. How can I do this in DAX? I've been trying to a while and can't seem to figure it out. Thanks!

[v-juanli-msft · Accepted solution]

Hi @JamesH245 

you can write your measure like so:

Trend =
LOOKUPVALUE (
    'Table'[Score],
    'Table'[Time], CALCULATE ( MAX ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
)
    - LOOKUPVALUE (
        'Table'[Score],
        'Table'[Time], CALCULATE ( MIN ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.