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

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

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