locked
PowerPivot - 3-Month Moving Average No Zeros RRS feed

  • Question

  • I am creating a dashboard and want to show the 3-month moving average.

    I have new items being added each month and using my current approach,when a new item is added, its value is being divided by 3 but it only has a score for 1 of the 3 months in range, so it is misleading.

    For example,  if the value of metric ABC is 1 for October 2014, and it was added in October 2014, it won't have values in September 2014 and August 2014 so it's 3-month moving average is is .33 if the end user selects October 2014 as the anchor month.

    I've followed this approach from PowerPivotpro.com. How do I get it to not include the months where there were no values?

    http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/

    Help??


    Johnny


    • Edited by jmc5319 Monday, February 2, 2015 1:42 AM
    Monday, February 2, 2015 1:42 AM

Answers

  • You can replace the "3" with a measure which calculates the number of months on the current filter context

    So somthing like:

    [CountMonthForMovingAvg]=CALCULATE(DISTINCTCOUNT(Calendar[MonthName]),DATESINPERIOD(Calendar[Date],LASTDATE(Calendar[Date]),-3,MONTH))

    Monday, February 2, 2015 6:21 AM
  • i would try something like this

    3M_Average:=AverageX( 
    FILTER(
    SUMMARIZE( 
        CALCULATETABLE(
            'Date';
             DATESINPERIOD('Date'[Date]; MAX('Date'[Date]);-3;MONTH));
        'Date'[Month Name]);
    [Sales] <> 0);
    [Sales]
    )

    there are plenty of blog-post which you can find via google, you simple need to extend them and add the FILTER()-part and/or the SUMMARIZE()-part to aggregate by month first

    hth
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, February 3, 2015 8:52 AM
    Answerer

All replies

  • You can replace the "3" with a measure which calculates the number of months on the current filter context

    So somthing like:

    [CountMonthForMovingAvg]=CALCULATE(DISTINCTCOUNT(Calendar[MonthName]),DATESINPERIOD(Calendar[Date],LASTDATE(Calendar[Date]),-3,MONTH))

    Monday, February 2, 2015 6:21 AM
  • i would try something like this

    3M_Average:=AverageX( 
    FILTER(
    SUMMARIZE( 
        CALCULATETABLE(
            'Date';
             DATESINPERIOD('Date'[Date]; MAX('Date'[Date]);-3;MONTH));
        'Date'[Month Name]);
    [Sales] <> 0);
    [Sales]
    )

    there are plenty of blog-post which you can find via google, you simple need to extend them and add the FILTER()-part and/or the SUMMARIZE()-part to aggregate by month first

    hth
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, February 3, 2015 8:52 AM
    Answerer