Forum Discussion

JamesH245's avatar
JamesH245
Frequent Visitor
7 years ago
Solved

DAX - find difference from first and last

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!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.