DATEADD issue using variables

[JuanSombrero]

Hi everybody,

I am running into the following issue. I am conviced it is related to me not propberly understanding (yet) how variables are being calculated, so any explenation would be greatly appreciated...

This is my DAX measure (it does not make sense by itself, I just slimmed it down to the essence of my issue). YearMonth is a field from a properly developed Calendar Table

Test =

VAR Sales = CALCULATE(SUM('Invoice detail'[Total excl. VAT]))

VAR Sales_LM = CALCULATE(Sales , DATEADD('Calendar'[Date],-1,MONTH))

return Sales_LM

As you can see in the screenshot, "Test" is returning the same result as my original sales measure, rather than the previous month. Anybody care to explain me why? I assume it has to do with the fact I refer to 'VAR Sales' in my 'VAR Sales_LM'? Because when I replace it by the initial Sales function, all works fine

Test =

VAR Sales = SUM('Invoice detail'[Total excl. VAT])

VAR Sales_LM = CALCULATE(SUM('Invoice detail'[Total excl. VAT]) , DATEADD('Calendar'[Date],-1,MONTH))

return Sales_LM

Many thx!

Regards,

Jan

[rbriga]

The variable is calculated once per context.

For example:

Distance from Average=
VAR _Avg = AVERAGE(Sales[Sales])
RETURN
AVERAGEX(
VALUES(Users[Id]),
SUM(Sales[Sales])-_Avg
)

Would return the average difference from the average of ALL selected users (rather than each individual user's average), unless the row context is the specific user.

[harshnathani]

Hi @JuanSombrero ,

Create this as a separate measure.

Sales Measure = SUM('Invoice detail'[Total excl. VAT])

and then try 

VAR Sales_LM = CALCULATE([Sales Measure], DATEADD('Calendar'[Date],-1,MONTH))

Regards,

Harsh Nathani

[JuanSombrero]

Hi Harsh,

that would indeed solve the issue. But could you explain me as well why my solution is not working? I have a feeling that the 'why' is very important here for me not to run into comparable issues in the future.

Thx
Jan

[harshnathani · Accepted solution]

Hi @JuanSombrero ,

Variables can be used anywhere but are evaluated in the context in which they are written.

You can refer the below blog for more understanding

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)