Forum Discussion

jamarston95's avatar
jamarston95
New Member
3 years ago

Calculate difference in value between consecutive dates

Hi all,   I have a table that looks something like this and I want to create a measure which will calculate the difference (NOT % difference) between similar rows but with the previous date.   ...
ERD's avatar
ERD
Community Champion
3 years ago

Hi. There are multiple ways to achieve this. Here is an example:

res = 
VAR prevValue =
    CALCULATE (
        MAX ( T1[Value] ),
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( T1 ),
                T1[Date] < MAX ( T1[Date] ) && T1[Geography] = MAX ( T1[Geography] ) && T1[Variable] = MAX ( T1[Variable] )
            ),
            T1[Date]
        )
    )
RETURN
    IF (
        NOT ISBLANK ( prevValue ) && ISINSCOPE ( T1[Date] ),
        MAX ( T1[Value] ) - prevValue
    )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello ERD 
    Your solution works. 
    But can you please let me know why you're using ISINSCOPE function in the Return query?
    To me it worked fine even if I didn't write the Isinscope function. 

    Hoping for a reply. 
    Thank you.
    PS: Just a curious learner of DAX.

    • ERD's avatar
      ERD
      Community Champion
      3 years ago

      It will make sure result is only shown when we have dates.

      Here is more details on it: ISINSCOPE – DAX Guide

  • jamarston95's avatar
    jamarston95
    New Member
    3 years ago

    Hi ERD ,

     

    Thanks for your response. I do have several other variables that I didn't include to simplify the question. I've altered your measure by replicating and adding: 

    && T1[Variable] = MAX ( T1[Variable] )

     for each extra variable in the FILTER expression. However, when I try this approach it is currently producing a blank value. It would be helpful if you could explain how prevValue is calculated? 

     

    My understanding is that the CALCULATE function is evaluating the max value, based on the top 1 date, filtered such that the date isn't the latest date. My confusion arises here:

    && T1[Geography] = MAX ( T1[Geography] ) && T1[Variable] = MAX ( T1[Variable] )

     Could you explain what this is doing?

     

    Thank you