Forum Discussion

JuanSombrero's avatar
JuanSombrero
Frequent Visitor
4 years ago
Solved

DATEADD issue using variables

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
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      JuanSombrero
      Frequent Visitor
      4 years ago

      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

  • rbriga's avatar
    rbriga
    Impactful Individual
    4 years ago

    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.