locked
Moving Average in PowerPivot with multiple entries for several dates. RRS feed

  • Question

  • Hello,

    I am having trouble completing the final formula for this moving average while following the steps from this link 

    http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=97. Anyone have any direction how to do it? Thanks!
    Monday, June 15, 2015 1:25 PM

Answers

  • Hi Kel1832,

    According to your description, you are trying to implement moving average in your PowerPivot data model, right?

    To get the moving average, we can get the moving SUM first, and then get the moving average by using moving sum divided by the number of months or years.
    [3 Month Moving Sum Units Sold] =
    CALCULATE([Units Sold],
              DATESINPERIOD(Calendar[Date],
                            LASTDATE(Calendar[Date]),-3, Month
                           )
             )

    3 Month Moving Avg Corrected]=
    [3 Month Moving Sum Units Sold] /
    CALCULATE(DISTINCTCOUNT(Calendar[Year Month]),
              DATESINPERIOD(Calendar[Date],
                            LASTDATE(Calendar[Date]),-3,Month
                           )
             )

    Please refer to the link below to see the details.
    http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Thursday, June 18, 2015 11:32 PM
    • Marked as answer by Charlie Liao Wednesday, June 24, 2015 2:04 AM
    Tuesday, June 16, 2015 2:01 AM