# 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?

Sincerely, Michael Girvin

Tuesday, April 18, 2017 11:12 PM

• 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 )
)
)```

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.

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 stand-alone 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,

• Edited by Thursday, April 20, 2017 9:56 AM Minor edit
• Marked as answer by 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 )
)
)```

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.

Wednesday, April 19, 2017 3:13 AM
• Hi Mike,

do you get an error-message - 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.com

Wednesday, April 19, 2017 5:53 AM
• I thought the main three reasons to use Variables were:

1) Clarity of code

2) 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 : (

Wednesday, April 19, 2017 6:24 PM
• 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 Girvin

Wednesday, 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 stand-alone 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,

• Edited by Thursday, April 20, 2017 9:56 AM Minor edit
• Marked as answer by 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/understanding-context-transition/

The use of variables makes a big difference in syntax once used where context transition takes place (so within a CALCULATE-statement for example). But within the example given here, we see actually not much difference.

Imke Feldmann
MVP Data Platform
TheBIccountant.com

Thursday, April 20, 2017 9:20 AM
• 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