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

[kingkong]

I want to calculate sales week over week by (sales of the current week selected - sales of the previous week) divided by sales of the previous week. But when I use the Sales variable in the SalesLW variable, the result returns a 0(Image 1).

But If I use sum(Query1[SalesAmount]) instead of variable Sales, I do get the accurate number. (Image 2) I thought that we can use a variable in another variable, why is it not returning the correct value? 

[BetterCallFrank · Accepted solution]

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 🙂

[v-yulgu-msft · Accepted solution]

Hi @kingkong ,

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

[BetterCallFrank · Accepted solution]

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 🙂