Rolling 3 Month Average of rows that need to be aggregated

[Anonymous]

Hi-

I'm trying to calcualte a rolling 3 month average of the total widgets shipped per month. I've found online help on how to calculate a moving average but I'm finding that I'm getting the wrong answer. I believe that since my data have multiple sizes per month the 3MMA calculation is getting confused. I need something that first sums the montly total of the multiple of sizes and then calculates the 3 month average.

I alo have multiple tables linked to a master "Calendar" table (Calendar = CALENDAR(MIN('Revenues'[Date]),MAX('Revenues'[Date])))

I used the following measures: 

Monthly Widget Shipments = SUM('Sample'[Total Widgets]) (this returns the correct monthly value)

I then used the following measure to calcuate the 3 month average of the above measure. 

3MMA =
CALCULATE (
AVERAGEX ( 'Sample', 'Sample'[Total Widgets] ),
DATESINPERIOD (
'Calendar'[Date],
LASTDATE ( 'Calendar'[Date] ),
-3,
MONTH
)
)                                         <---but this does not return the correct value 

I get: 

Year	Month	Total Widgets	3MMA
1995	January	39749.95	883.33
1995	February	32164.81	799.05
1995	March	27386.31	735.56
1995	April	23995.52	618.86
1995	May	29757.07	601.02
1995	June	28136.16	606.58
1995	July	23935.69	606.14

"Sample" table:

Date	Size	Type	North America	Europe	Japan	Asia-Pacific	Taiwan	Other Asia
Jan-95	< = 4.5"	Epi	112.4142335	207.1568982	2054.860107	2592.633	0	0
Jan-95	< = 4.5"	Polished	117.8424424	203.9814163	2131.008545	2916.712	0	0
Jan-95	>= 6.51"	Epi	106.0821647	209.2931316	1978.378906	2303.828	0	0
Jan-95	>= 6.51"	Polished	100.4726338	203.9814163	1932.455566	2074.548	0	0
Jan-95	4.51-5.5"	Epi	96.59112156	207.6187865	1775.803711	2076.752	0	0
Jan-95	4.51-5.5"	Polished	93.44502151	206.5795379	1668.960449	2006.204	0	0
Jan-95	5.51-6.5"	Epi	97.76392625	199.5357415	1758.074951	2175.96	0	0
Jan-95	5.51-6.5"	Polished	97.10641057	185.3326765	1783.627197	2081.161	0	0
Jan-95	NA	Non-Polished	94.09347357	185.5058846	1655.065674	2059.115	0	0
Feb-95	< = 4.5"	Epi	93.07671086	216.5101361	1626.1521	2045.887	0	0
Feb-95	< = 4.5"	Polished	89.51329764	238.1611497	1503.910156	2010.614	0	0
Feb-95	>= 6.51"	Epi	85.65393566	226.9603587	1430.656738	1878.337	0	0
Feb-95	>= 6.51"	Polished	84.71681296	224.8818614	1430.82959	1876.132	0	0
Feb-95	4.51-5.5"	Epi	83.88587124	213.6233343	1452.381104	1803.379	0	0
Feb-95	4.51-5.5"	Polished	83.98844577	211.8335172	1442.951904	1816.607	0	0
Feb-95	5.51-6.5"	Epi	82.95270121	207.5033145	1369.234619	1823.221	0	0
Feb-95	5.51-6.5"	Polished	80.47742747	193.7044018	1296.99585	1746.059	0	0
Feb-95	NA	Non-Polished	78.19089278	183.6583315	1232.406738	1699.762	0	0
Mar-95	< = 4.5"	Epi	77.09715521	183.8315396	1175.311279	1682.125	0	0
Mar-95	< = 4.5"	Polished	80.12592923	185.9677729	1230.828857	1787.947	0	0
Mar-95	>= 6.51"	Epi	77.45823218	184.6398441	1168.911133	1706.376	0	0
Mar-95	>= 6.51"	Polished	75.74860988	189.5474072	1140.181641	1664.488	0	0
Mar-95	4.51-5.5"	Epi	74.26850785	197.9191324	1081.846436	1651.26	0	0
Mar-95	4.51-5.5"	Polished	75.59330561	197.1108279	1130.884277	1655.67	0	0
Mar-95	5.51-6.5"	Epi	76.21715322	196.5334676	1113.258545	1613.782	0	0
Mar-95	5.51-6.5"	Polished	75.46466258	174.1318855	1087.706299	1596.145	0	0
Mar-95	NA	Non-Polished	72.12837795	164.2012872	1029.236572	1512.369	0	0
Apr-95	< = 4.5"	Epi	70.28078157	176.9609513	996.2075195	1521.188	0	0
Apr-95	< = 4.5"	Polished	69.73296137	193.0693054	973.2941895	1530.006	0	0
Apr-95	>= 6.51"	Epi	64.89035172	189.8360874	918.8493652	1309.544	0	0
Apr-95	>= 6.51"	Polished	66.95429328	183.0232351	957.8479004	1439.617	0	0
Apr-95	4.51-5.5"	Epi	66.64782224	180.4828495	958.8640137	1450.64	0	0
Apr-95	4.51-5.5"	Polished	66.10299901	191.1062802	959.5319824	1424.185	0	0
Apr-95	5.51-6.5"	Epi	66.08449868	193.1270414	951.4841309	1463.868	0	0
Apr-95	5.51-6.5"	Polished	66.05670376	182.965499	965.1469727	1444.026	0	0
Apr-95	NA	Non-Polished	66.90749256	174.7092458	987.3874512	1474.891	0	0
May-95	< = 4.5"	Epi	69.34204749	176.9609513	1076.530762	1574.099	0	0
May-95	< = 4.5"	Polished	73.87688108	168.0696017	1230.455811	1649.055	0	0
May-95	>= 6.51"	Epi	75.57017217	163.5084548	1301.729248	1598.35	0	0
May-95	>= 6.51"	Polished	78.21938756	171.4182918	1363.567139	1675.511	0	0
May-95	4.51-5.5"	Epi	81.40060871	169.5130026	1453.434082	1765.901	0	0
May-95	4.51-5.5"	Polished	80.61640635	173.1503729	1465.578369	1701.967	0	0
May-95	5.51-6.5"	Epi	81.93053309	176.4159131	1520.209961	1704.171	0	0
May-95	5.51-6.5"	Polished	83.25443391	177.5969771	1603.66748	1640.237	0	0
May-95	NA	Non-Polished	82.81556289	174.3468659	1613.72876	1560.871	0	0
Jun-95	< = 4.5"	Epi	80.24953947	170.8212499	1566.258057	1435.208	0	0
Jun-95	< = 4.5"	Polished	78.28813773	168.0870849	1516.727783	1388.911	0	0
Jun-95	>= 6.51"	Epi	78.99799722	165.7346539	1549.339111	1415.366	0	0
Jun-95	>= 6.51"	Polished	77.43798463	168.4707998	1548.90625	1375.683	0	0
Jun-95	4.51-5.5"	Epi	76.05308569	169.5299728	1491.161621	1428.593	0	0
Jun-95	4.51-5.5"	Polished	76.4787245	171.7434059	1455.762939	1501.346	0	0
Jun-95	5.51-6.5"	Epi	75.30334423	171.6729979	1370.350342	1532.211	0	0
Jun-95	5.51-6.5"	Polished	72.27812968	171.0067158	1289.13501	1419.775	0	0
Jun-95	NA	Non-Polished	71.04938972	168.5328004	1275.029053	1364.66	0	0
Jul-95	< = 4.5"	Epi	67.76015646	162.1907337	1162.806152	1331.591	0	0
Jul-95	< = 4.5"	Polished	67.14890774	163.8537262	1136.009033	1338.204	0	0
Jul-95	>= 6.51"	Epi	64.08946392	164.7820242	1012.169922	1294.112	0	0
Jul-95	>= 6.51"	Polished	63.46885977	162.7280883	1027.647949	1272.066	0	0
Jul-95	4.51-5.5"	Epi	66.89670655	160.3750808	1154.6604	1344.818	0	0
Jul-95	4.51-5.5"	Polished	65.31062346	156.2555162	1095.696045	1320.567	0	0
Jul-95	5.51-6.5"	Epi	65.3395327	154.7887733	1075.854492	1358.046	0	0
Jul-95	5.51-6.5"	Polished	66.15478935	160.20072	1097.900879	1388.911	0	0
Jul-95	NA	Non-Polished	66.46749596	162.2091007	1095.255127	1389.352	0	0

Desired output:

	Monthly Total	3MMA
Jan-95	39749.95
Feb-95	32164.81
Mar-95	27386.31	33100.36
Apr-95	23995.52	27848.88
May-95	29757.07	27046.3
Jun-95	28136.16	27296.25
Jul-95	23935.69	27276.31

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[fyoung]

Hi Ashish, I couldn't download from the link, tried both company PC and personal PC at home. Is it still available? If not could you please upload again? Thank you very much.

[mattbrice · Accepted solution]

You need to do something like this:

3MMA = 
CALCULATE(  
   AVERAGEX( VALUES( 'Calendar'[Month-Year] ), [Sum of Total Widgets] ), 
   DATESINPERIOD( 'Calendar'[Date], MAX( 'Calendar'[Date] ), -3, MONTH ) )

[Anonymous]

Thank you Matt! It works perfectly.

-lara