none
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 kostaz Tuesday, September 10, 2013 7:32 PM
    Tuesday, September 10, 2013 3:15 PM

Answers

  • 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 kostaz Wednesday, September 11, 2013 5:08 PM
    Wednesday, September 11, 2013 9:16 AM
    Answerer