PowerPivot DAX: Moving Average

Standard

A quick blog post about sharing PowerPivot DAX formula to calculate moving average. Please use this as building block for your scenario:

Here’s the Data:

DATEAMOUNT
6/1/201350
6/2/201340
6/3/201330
6/4/201320
6/5/201310
6/6/201310
6/7/201320
6/8/201330
6/9/201340
6/10/201350

(usually, the date would in a date table. For the purpose of sharing building block of this formula, I kept it this way to keep it simple)

Formula (Calculated Measure):

[code language=”text”]
Moving Average Last 3 days:=CALCULATE(AVERAGE(TableName[Amt]),DATESINPERIOD(TableName[date],PREVIOUSDAY(TableName[date]),-3,day))
[/code]

Results via a Pivot Table: powerpivot dax moving average

Note that the formula is a building block and you’ll have to make changes as per your requirement and data model. Feel free to leave a comment if you need some assistance from my side. And also consider using the PowerPivot forum to reach out to community: MSDN Forum – PowerPivot for Excel

Note from Paras: To support this blog and free content here, I partnered with Datacamp for affiliate marketing revenue. I personally use this platform (see my profile here) and highly recommend it. I wouldn't be endorsing them if it wasn't 10/10! Click on the link below to start your journey towards becoming the BEST data professional! datacampParas's datacamp referral link

0 thoughts on “PowerPivot DAX: Moving Average

  1. Joonas Leppänen

    Dear Paras Doshi,
    I have been struggling with these moving averages for quite some time with many different type of power pivot tables.
    For example my data may look like this, my original table is called “MonthlySummary_TAB”:
    |Year|Month|Type|Volume| Date |
    |2015| 1 | A | 1000 | 1.1.2015 |
    |2015| 1 | B | 2000 | 1.1.2015 |
    |2015| 2 | A | 2000 | 1.2.2015 |
    |2015| 2 | B | 4000 | 1.2.2015 |
    |2015| 3 | A | 3000 | 1.2.2015 |
    |2015| 3 | B | 6000 | 1.2.2015 |
    where the Date column is constructed from Month and Table by:
    =FORMAT([Year] & “/” & FORMAT(INT([Month]);”0#”);”yyyy/mm”)

    Of course the data is more complex than here but the basic idea remains that I have volumeric data for each month by product type.
    I would like to calculate a moving average of 3 previous months and would like to show the data in pivot tables for each type, for example for A type:
    |Year|Month|Volume|Volume AVG|
    |2015| 1 | 1000 | 1000 |
    |2015| 2 | 2000 | 1500 |
    |2015| 3 | 3000 | 2000 |

    I have tried different calculated fields to do this but none of them have worked.
    My latest try is:
    Moving Average Last 3 Months:=CALCULATE(AVERAGE(MonthlySummary_TAB[Volume]); DATESINPERIOD(MonthlySummary_TAB[Date]; DATEADD(MonthlySummary_TAB[Date];-3;month);3; month))

    Can you help me out with this issue?

What do you think? Leave a comment below.