Moving average based on 'hidden' data

[nielf]

Hi,

I have create a Power PivotTable (2010) with sales data from each month from 2011 - today. From the PivotTable I have created a PivotChart.

I only want to see sales data from the last 13 months in my chart so i have created a report filter for that. However, I would also like to see a 12 months moving average in the chart.

The problem is, that since I'm only showing data from the last 13 months in my chart due to my report filter, Excel cannot calculate the moving average for all the months in the chart - even though the data is available but 'hidden'. Is there any workaround for this?

 

[DRSteele]

I encountered this very issue once. I couldn't figure out how to make the charting feature grab the so-called hidden data. So I created a helper column that contained the 12-mo MA and added it as a series to the chart.

 

[nielf]

I encountered this very issue once. I couldn't figure out how to make the charting feature grab the so-called hidden data. So I created a helper column that contained the 12-mo MA and added it as a series to the chart.

That was also my initial thought. I just need to figure out how to do that for my 200,000 rows in PowerPivot. I would like to end up with a solution where I'm able to slice the data.

 

[scottsen]

It typically looks something like:

12MoAvg := CALCULATE([Total], FILTER(ALL(Calendar), Calendar[MonthNum] > MAX(Calendar[MonthNum]) - 11)) / 12

It can break down a bit if your data doesn't go far enough back, in which case you need to massage it a bit to divide by the number of months you actually HAVE...

 

[nielf]

Thanks for the reply scottsen.

I have been on vacation so I haven't had time to play around with it yet but I hope your formula can bring me in the right direction.

 

[nielf]

I'm a bit stuck here. I haven't been able to figure out how to calculate the 12 Months Moving Average although you tried to guide me, scottsen.

Perhaps you can help me by looking at this sample file. In the file I have calculated Hitrate of the Month based on sum and count using the formulas below:

SUM:
=CALCULATE(SUM(Data[SalesPrice]),Data[Status]="Awarded")/((CALCULATE(SUM(Data[SalesPrice]),Data[Status]="Awarded"))+CALCULATE(SUM(Data[SalesPrice]),Data[Status]="Lost"))

COUNT:
=COUNTROWS(FILTER(Data,Data[Status]="Awarded"))/(COUNTROWS(FILTER(Data,Data[Status]="Awarded"))+COUNTROWS(FILTER(Data,Data[Status]="Lost")))

I've come this far which is great but the real value lies in calculating the 12 months moving average and I can't figure that out.