# 12 months moving average measure

1. ## 12 months moving average measure

With my limited knowledge of PopwerPivot I'm trying to create two measures that can calculate a 12 months moving average of my hitrate.

I have succeeded in creating two measures that calculate 'hitrate of the month' based on both SUM and COUNT. My definition of hitrate in this case is: Awarded / (Awarded + Lost) = Hitrate.

The two measures looks like this:

SUM:
Formula:
`Please Login or Register  to view this content.`

COUNT:
Formula:
`Please Login or Register  to view this content.`

Capture.PNG

Now I'm trying to create two measures that calculate a 12 months moving average for both SUM and COUNT. However, this time around I have no idea where to start.

I'm using a fairly simple sample file for this purpose. It only contain one table and four columns. The file can be downloaded here:

Sample2.xlsx

I hope you can help me or give me a push in the right direction.

Best regards,
Nielf

2. ## Re: 12 months moving average measure

Hi
external links being potentially unsafe and unreachable for some members, please post your file on the forum - Thx

3. ## Re: 12 months moving average measure

I know PT's are extremely powerful, but just sometimes it might be better to use formulas. I think maybe this may be 1 of those times.

I created a small table, with consecutive dates, and then used SUMIFS and COUNTIFS, with EDATE() built in...
 E F G 3 1/1/2015 51.09% 57.14% 4 2/1/2015 16.91% 20.00% 5 3/1/2015 51.19% 40.00% 6 4/1/2015 50.03% 50.00% 7 5/1/2015 56.55% 60.00% 8 6/1/2015 42.48% 33.33% 9 7/1/2015 77.74% 66.67% 10 8/1/2015 29.28% 40.00% 11 9/1/2015 36.75% 50.00%

F3=SUMIFS(Data!\$B\$2:\$B\$114,Data!\$D\$2:\$D\$114,"awarded",Data!\$C\$2:\$C\$114,">="&Hitrate!E3,Data!\$C\$2:\$C\$114,"<"&EDATE(E3,1))/(SUMIFS(Data!\$B\$2:\$B\$114,Data!\$D\$2:\$D\$114,"lost",Data!\$C\$2:\$C\$114,">="&Hitrate!E3,Data!\$C\$2:\$C\$114,"<"&EDATE(E3,1))+SUMIFS(Data!\$B\$2:\$B\$114,Data!\$D\$2:\$D\$114,"awarded",Data!\$C\$2:\$C\$114,">="&Hitrate!E3,Data!\$C\$2:\$C\$114,"<"&EDATE(E3,1)))

G3=COUNTIFS(Data!\$D\$2:\$D\$114,"awarded",Data!\$C\$2:\$C\$114,">="&Hitrate!E3,Data!\$C\$2:\$C\$114,"<"&EDATE(E3,1))/(COUNTIFS(Data!\$D\$2:\$D\$114,"lost",Data!\$C\$2:\$C\$114,">="&Hitrate!E3,Data!\$C\$2:\$C\$114,"<"&EDATE(E3,1))+COUNTIFS(Data!\$D\$2:\$D\$114,"awarded",Data!\$C\$2:\$C\$114,">="&Hitrate!E3,Data!\$C\$2:\$C\$114,"<"&EDATE(E3,1)))
Both copied down

For the 12-month average, change all the EDATE() from ,1 to ,12

4. ## Re: 12 months moving average measure

Hi FDibbins,

Thanks for your reply. One of the reasons I'm using PivotTables is that I need to be able to slice the data. Can I do that with the solution you have come up with? I don't have Excel on the computer I'm replying from so I haven't been able to test it yet.

5. ## Re: 12 months moving average measure

I found a solution.

I had to create a Calendar Table and then I used the logic described here:

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

You can see the result here:

Hitrate.xlsx

There are currently 1 users browsing this thread. (0 members and 1 guests)