topic Re: Calculate difference in value between consecutive dates in DAX Commands and Tips

Calculate difference in value between consecutive dates

jamarston95 — Wed, 22 Feb 2023 10:32:48 GMT

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

Re: Calculate difference in value between consecutive dates

ERD — Wed, 22 Feb 2023 18:46:01 GMT

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 )

Re: Calculate difference in value between consecutive dates

Anonymous — Thu, 23 Feb 2023 05:37:12 GMT

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.

Re: Calculate difference in value between consecutive dates

ERD — Thu, 23 Feb 2023 07:05:02 GMT

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

Here is more details on it: ISINSCOPE – DAX Guide

Re: Calculate difference in value between consecutive dates

jamarston95 — Mon, 27 Feb 2023 12:29:04 GMT

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