Your measure does not work because you are using a variable as the expression parameter of CALCULATE: variables are immutable; that means that once defined the behave like a constant, that means that their value cannot be changed.
Variables are evaluated where they are defined and not where they are referenced; therefore their value is not affected by modified filter context in CALCULATE.
To check your formula use directly the measure instead of the variable
Sales Last Year =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR( CalendarTable[Date] )
)
This way the [Total Sales] is evaluated in the filter context altered by SAMEPERIODLASTYEAR( CalendarTable[Date] )
using a variable instead, like in
Sales Last Year (wrong) =
VAR Sales = [Total Sales]
RETURN
CALCULATE(
Sales,
SAMEPERIODLASTYEAR( CalendarTable[Date] )
)
makes Sales to be evaluated outside CALCULATE
, using the filter context existing where the measure is evaluated. Assuming that this value is 1000
the following CALCULATE
expression is equivalent to
CALCULATE(
1000,
SAMEPERIODLASTYEAR( CalendarTable[Date] )
)
that will return 1000 whatever the SAMEPERIODLASTYEAR
is
2
solved DAX: please explain why this measure with a variable will not work [closed]