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:
| DATE | AMOUNT |
| 6/1/2013 | 50 |
| 6/2/2013 | 40 |
| 6/3/2013 | 30 |
| 6/4/2013 | 20 |
| 6/5/2013 | 10 |
| 6/6/2013 | 10 |
| 6/7/2013 | 20 |
| 6/8/2013 | 30 |
| 6/9/2013 | 40 |
| 6/10/2013 | 50 |
(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: 
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
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?