Calculate difference in value between consecutive dates

[jamarston95]

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.

Date	Geography	Variable	Value
01 December 2022	England	A	10
01 December 2022	Scotland	A	20
01 December 2022	England	B	30
01 December 2022	Scotland	B	15
01 September 2022	England	A	20
01 September 2022	Scotland	A	15
01 September 2022	England	B	35
01 September 2022	Scotland	B	25

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:

Date	Geography	Variable	Value	Diff
01 December 2022	England	A	10	-10 (10-20)
01 December 2022	Scotland	A	20	5 (20-15)
01 December 2022	England	B	30	-5 (30-35)
01 December 2022	Scotland	B	15	-10 (15-25)
01 September 2022	England	A	20
01 September 2022	Scotland	A	15
01 September 2022	England	B	35
01 September 2022	Scotland	B	25

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

[ERD]

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
    )

[jamarston95]

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

[Anonymous]

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]

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

Here is more details on it: ISINSCOPE – DAX Guide