[Solved] DAX: please explain why this measure with a variable will not work [closed]


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]