Answered by:
How Do I Use DAX Variables in Excel 2016 Power Pivot, VAR RETURN?
Question

Dear Team,
I have been hearing about the ability to use Variables in DAX in Excel 2016 for a while, but I just can't figure out how to do it. I have searched the internet and I can't find any example for Excel.
With NO Variable this formula works:
12MonthRollingAverage03:=
IF (
MAX ( dDate[Year] ) > 2016,
CALCULATE (
AVERAGE ( fSales[Sales] ),
DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR )
)
)With a Variable, this formula returns a blank in all cells in the PivotTable:
12MonthRollingAverage02:=
VAR MinYear = MIN ( dDate[Year] )
RETURN
IF (
MAX ( dDate[Year] ) > MinYear,
CALCULATE (
AVERAGE ( fSales[Sales] ),
DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR )
)
)Is my syntax correct for using a variable in Excel? Or is something else wrong?
Thank you in advance.
Sincerely, Michael Girvin
Tuesday, April 18, 2017 11:12 PM
Answers

Hi Mike,
After research, your saytax is right. You'd better confirm if the result of DAX part aligns with your requirement. I thank you add the [Year] as field, and the [12MonthRollingAverage02] as measure, it return blank, right? If it is, because the DAX is affected by context. For each year, MIN ( dDate[Year] ) and MAX ( dDate[Year] ) return the same value, so the logical condition in If is false, so it return the blank. Please change the condition as "MAX ( dDate[Year] ) >= MinYear", and check if it works fine.
In addition, if you want to get the min year of all years, you should add a filter in MIN function, like the following formula.
12MonthRollingAverage02:= VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate)) RETURN IF ( MAX ( dDate[Year] ) > MinYear, CALCULATE ( AVERAGE ( fSales[Sales] ), DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR ) ) )
Please feel free to ask if you have other questions.
Best Regards,
Angelia
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
 Edited by Angelia ZhangMicrosoft contingent staff Wednesday, April 19, 2017 3:16 AM
 Proposed as answer by Michael Amadi Thursday, April 20, 2017 9:25 AM
 Marked as answer by Mike GirvinMVP Thursday, April 20, 2017 11:55 PM
Wednesday, April 19, 2017 3:13 AM 
Hi Mike,
What you've understood so far is correct. The DAX variable will be evaluated within the original/first evaluation context. So, for a PivotTable, the context would be defined by the rows, columns, filters, and slicers.
Whatever the variable's expression would return as a standalone measure, for a given cell, is the same value it will return when it is evaluated as a variable.
In your example scenario, this variable...
VAR MinYear = MIN ( dDate[Year] )
...will return the same value as this measure...
MinYear := MIN ( dDate[Year] )
...for a given cell.
If you create the above measure and place it in your PivotTable, you will be looking at what the variable expression is, or would be, returning for each cell. It becomes even clearer why the following doesn't work as expected...
12MonthRollingAverage02:= VAR MinYear = MIN ( dDate[Year] ) RETURN IF ( MAX ( dDate[Year] ) > MinYear, CALCULATE ( AVERAGE ( fSales[Sales] ), DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR ) ) )
...And why the variable based on what Angelia suggested, and what you previously attempted, does...
12MonthRollingAverage02:= VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate)) RETURN IF ( MAX ( dDate[Year] ) > MinYear, CALCULATE ( AVERAGE ( fSales[Sales] ), DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR ) ) )
To summarise, in order to expand the evaluation context beyond the original one (defined by the rows, columns, filters, etc.), we still need to use the CALCULATE/CALCULATETABLE function arguments when defining variables.
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearn
 Edited by Michael Amadi Thursday, April 20, 2017 9:56 AM Minor edit
 Marked as answer by Mike GirvinMVP Thursday, April 20, 2017 11:55 PM
Wednesday, April 19, 2017 11:59 PM
All replies

Hi Mike,
After research, your saytax is right. You'd better confirm if the result of DAX part aligns with your requirement. I thank you add the [Year] as field, and the [12MonthRollingAverage02] as measure, it return blank, right? If it is, because the DAX is affected by context. For each year, MIN ( dDate[Year] ) and MAX ( dDate[Year] ) return the same value, so the logical condition in If is false, so it return the blank. Please change the condition as "MAX ( dDate[Year] ) >= MinYear", and check if it works fine.
In addition, if you want to get the min year of all years, you should add a filter in MIN function, like the following formula.
12MonthRollingAverage02:= VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate)) RETURN IF ( MAX ( dDate[Year] ) > MinYear, CALCULATE ( AVERAGE ( fSales[Sales] ), DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR ) ) )
Please feel free to ask if you have other questions.
Best Regards,
Angelia
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
 Edited by Angelia ZhangMicrosoft contingent staff Wednesday, April 19, 2017 3:16 AM
 Proposed as answer by Michael Amadi Thursday, April 20, 2017 9:25 AM
 Marked as answer by Mike GirvinMVP Thursday, April 20, 2017 11:55 PM
Wednesday, April 19, 2017 3:13 AM 
Hi Mike,
do you get an errormessage  if yes: What does it say?
Or does it not return the expected result? Then: What do you expect to see and what is showing instead?
Imke Feldmann
MVP Data Platform
TheBIccountant.comWednesday, April 19, 2017 5:53 AMAnswerer 
Thank you for answering, Angelia!
I thought the main three reasons to use Variables were:
1) Clarity of code2) Sometimes there is a performance benefit
3) That the Variable Formula calculates in a context where it is defined instead of where it is used.
In one of my earlier attempts at this formula, rather than the hard coded value 2016, I used what you suggested, CALCULATE(MIN ( dDate[Year] ),ALL(dDate)). But I was trying to replace it by using a variable that would calculate the value outside the PivotTable Context, where it was originally defined.
I am stunned to hear that the Variable can see the context in the PivotTable!?!?
Now I am really confused : ( Edited by Mike GirvinMVP Wednesday, April 19, 2017 6:35 PM
Wednesday, April 19, 2017 6:24 PM 
To answer your other two points:
1) Year and Month are on the Row Area of the PivotTable. That is not the problem because both of these formulas work:
12MonthRollingAverage03:=IF (
MAX ( dDate[Year] ) > 2016,
CALCULATE (
AVERAGE ( fSales[Sales] ),
DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR )
)
)and
12MonthRollingAverage03:=IF (
MAX ( dDate[Year] ) > CALCULATE(MIN(dDate[Year]),ALL(dDate[Year])),
CALCULATE (
AVERAGE ( fSales[Sales] ),
DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR )
)
)2) This was not what I wanted: MAX ( dDate[Year] ) >= MinYear, becasue I wanted to exclude only the first year in the data set.
Thank you again for trying to help me, Angelia.
Sincerely, Michael GirvinWednesday, April 19, 2017 6:29 PM 
Thank you for your response. I did not get an error, just a blank result.Wednesday, April 19, 2017 6:30 PM

Hi Mike,
What you've understood so far is correct. The DAX variable will be evaluated within the original/first evaluation context. So, for a PivotTable, the context would be defined by the rows, columns, filters, and slicers.
Whatever the variable's expression would return as a standalone measure, for a given cell, is the same value it will return when it is evaluated as a variable.
In your example scenario, this variable...
VAR MinYear = MIN ( dDate[Year] )
...will return the same value as this measure...
MinYear := MIN ( dDate[Year] )
...for a given cell.
If you create the above measure and place it in your PivotTable, you will be looking at what the variable expression is, or would be, returning for each cell. It becomes even clearer why the following doesn't work as expected...
12MonthRollingAverage02:= VAR MinYear = MIN ( dDate[Year] ) RETURN IF ( MAX ( dDate[Year] ) > MinYear, CALCULATE ( AVERAGE ( fSales[Sales] ), DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR ) ) )
...And why the variable based on what Angelia suggested, and what you previously attempted, does...
12MonthRollingAverage02:= VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate)) RETURN IF ( MAX ( dDate[Year] ) > MinYear, CALCULATE ( AVERAGE ( fSales[Sales] ), DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), 1, YEAR ) ) )
To summarise, in order to expand the evaluation context beyond the original one (defined by the rows, columns, filters, etc.), we still need to use the CALCULATE/CALCULATETABLE function arguments when defining variables.
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearn
 Edited by Michael Amadi Thursday, April 20, 2017 9:56 AM Minor edit
 Marked as answer by Mike GirvinMVP Thursday, April 20, 2017 11:55 PM
Wednesday, April 19, 2017 11:59 PM 
I'm very impressed by Michael's description. It makes sense to me, because I'm aware of the "elephant in the room" here, called "Context transition".
In case you're not familiar with it, this might be a good start: https://www.sqlbi.com/articles/understandingcontexttransition/
The use of variables makes a big difference in syntax once used where context transition takes place (so within a CALCULATEstatement for example). But within the example given here, we see actually not much difference.
Imke Feldmann
MVP Data Platform
TheBIccountant.comThursday, April 20, 2017 9:20 AMAnswerer 
Thank you for taking the time to answer my questions, Michael Amadi! I was mistakenly assuming that original context was the Measure grid where I created the formula. From what you say, now I see that each cell in Pivot is the original context.
Thursday, April 20, 2017 11:54 PM