Measure not returning value when using a variable to calculate another variable

Hey kingkong,

basically, a variable is evaluated one time and the resulting value is assigned to the variable; the variable expression is not evaluated over and over again when you use the variable.

So

VAR vSales = SUM( q1[sales] ) // evaluated one time, e.g. vSales = 10
VAR vSalesLWnotworking = CALCULATE ( vSales; DATEADD( dimdate[date], -7, day ) ) // will always return 10
VAR vSalesLWworks = CALCULATE ( SUM( q1[sales] ); DATEADD( dimdate[date], -7, day ) ) // will work as expected

You can re-use the value of the variable, but not the expression of the variable

Hope this helps :-)

Hi Anonymous ,

It is a Row Context related behavior.

Scenario 1: Definde Sum(Query1[SalesAmount]) in an outside variable 'Sales', and refer to this variable in another variable 'SalesLW'.

There existing two levels of row context in current DAX formula. The outside row context is determined by current row in table visual. The inside row context is determined by the DATEADD expression. The result of "Sum(Query1[SalesAmount])" is influenced by the outside row context (current row in table visual, "5/6/2019"), as it is defined outside. So, "SalesLW" returns 13 in this case, "SalesWoW" returns (13-13)/13=0.

Scenario 2: Define Sum(Query1[SalesAmount]) directly in current variable 'SalesLW'.

Also, there existing two levels of row context in current DAX formula. But "Sum(Query1[SalesAmount])" is influenced by the inside row context (DATEADD('Date'[Date],-7,DAY)=4/29/2019). So, "SalesLW" returns 20 in this case, "SalesWoW" returns (13-20)/20=-35%.

Here are some links written about row context for your reference:

Context in DAX Formulas

Row context and Filter context in DAX

Row Context, Nested Functions, and EARLIER() in PowerPivot and DAX

Best regards,

Yuliana Gu