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.

 

DateGeographyVariableValue
01 December 2022EnglandA10
01 December 2022ScotlandA20
01 December 2022EnglandB30
01 December 2022ScotlandB15
01 September 2022EnglandA20
01 September 2022ScotlandA15
01 September 2022EnglandB35
01 September 2022ScotlandB25

 

I have tried several different approaches and for reasons I am unable to understand they seem to simply be returning the original value. For instance, 

 

 

 

Diff =

VAR shifted_values = CALCULATE ( SUM('Table'[Value]), DATEADD ( 'Table'[Date].[Date], -3, MONTH ) )

RETURN 

CALCULATE ( SUM ('Table'[Value]) - shifted_values)

 

 

The output I am looking for would be:

 

DateGeographyVariableValueDiff
01 December 2022EnglandA10-10 (10-20)
01 December 2022ScotlandA205 (20-15)
01 December 2022EnglandB30-5 (30-35)
01 December 2022ScotlandB15-10 (15-25)
01 September 2022EnglandA20 
01 September 2022ScotlandA15 
01 September 2022EnglandB35 
01 September 2022ScotlandB25 

 

Note: And assuming there were no values before 01 September 2022 this would have no values.

 

It feels like this shouldn't be hard to do and is almost certainly me being an idiot when it comes to DAX but any suggestions/guidance would be greatly appreciated.

 

Thank you

 

4 Replies

  • 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.

    • 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