# Calculating moving average

### Question

• Let's say I have a table that stores quarterly sales data. How would I construct a DAX formula to get a rolling average of last 5 years on quarter by quarter basis? My below formula seems to only give results for the last 5 years for ALL quarters. What I want is the average of Q1 this year, Q1 last year, Q1 2 years ago, and so on until 5 years ago from current row.

5 Year Average of Quarterly Changes:=IF(HASONEVALUE(DimDate[DateKey]), CALCULATE( AVERAGEX( DATESINPERIOD(DimDate[DateKey], DATEADD(DimDate[DateKey], -5, YEAR), 5, YEAR), [Quarter on Quarter Change] )))

• Edited by Tuesday, September 10, 2013 7:32 PM
Tuesday, September 10, 2013 3:15 PM

• taking AdventureWorks as an example you may use this calculation:

`AvgSA_2Y:=AVERAGEX(TOPN(    2,    FILTER(ALL('Date'[Calendar Year]), 'Date'[Calendar Year] <= MAX('Date'[Calendar Year])),    'Date'[Calendar Year]),CALCULATE(SUM('Internet Sales'[Sales Amount])))`

first we select all years before the current year
then we select the top X (=5 in your case) years which returns the last 5 years
over this set of years the Average is calculated

important to note is that all other filters keep in place for this calculation!
so if you put your "Quarter of Year" column into your pivot-table this will be calculated for each Quarter

this also works with "Month of Year"

it is also important that you use the "Quarter of Year"-column (e.g. Q1, Q2, ...) and not the "Quarter"-column (e.g. Q1 2013, Q2 2013, ...)

hth,
gerhard

- www.pmOne.com -

• Marked as answer by Wednesday, September 11, 2013 5:08 PM
Wednesday, September 11, 2013 9:16 AM