Dateadd does not work in DAX in variable

[bastienmol]

Dear all

I have this formula 

Sales Last Week  = CALCULATE(sum('Daily KPI'[sales]),DATEADD('Daily KPI'[date],-7,DAY))

This one is working - it gives me sales for Last Week

However when I try this one, it gives me the value as the dateadd is not there. 

Sales Last Week  = 

var sales = sum('Daily KPI'[sales])

return CALCULATE(sales,DATEADD('Daily KPI'[date],-7,DAY))

Could you please help me to understand why ?

Thanks a lot for your help

[lucadelicio]

Hi,
1. the calculate function return the calculate of the measure in the context you specify with the filter.
So in the first one you're tell to the pbi to calculate the sum with the specify filter (date - 7 day).
2. In the second you do the calculate of the measure in the variable. And in the variable you're not specify any filter context like the data. So the sum return a value that you pass to the calculate ignoring any filter beacause you're passing a result not a measure to calculate.

If you want the sum for the previous week the correct measure is the first.
I hope I was clear.
Enjoy

[bastienmol]

Thanks a lot for the explanation.

So what I should put in the 2nd formula (with the variables) to make it work ? I still wish to keep the dateadd in the final variable

Thank you again 

[lucadelicio]

You have to do the calculate in the variable.
And then return the variable.

var sales = CALCULATE(sum('Daily KPI'[sales]),DATEADD('Daily KPI'[date],-7,DAY))
RETURN sales

Enjoy
mark as a solution if you resolve

[bastienmol]

and is there a way to pass a measure, instead of a result ?

in a way that I can keep return CALCULATE(me,DATEADD('Daily KPI'[ga_date],-7,DAY)) 

The reason for this is in reality my formula in more complexe and I call the sales metrics more than once. So the idea was to push it into a variable, then call the variable. 

thank you again 

[lucadelicio · Accepted solution]

You have to pass a measure that select a different measure different by case (not a variable).
Something like this:
MEASURE SWITCH = IF(x > 1, Measure1, Measure2)

MEASURE = CALCULATE(MEASURE SWITCH, DATEADD('Daily KPI'[ga_date],-7,DAY))

So the MEASURE will return the calculate of the measure switch that it will be Measure1 or Measure2.

I hope i'll be helpful
mark as a solution if you resolve
Ciao!

[bastienmol]

appreciate the time spent - thanks again