cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Rolling 3 Month Average of rows that need to be aggregated

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

1 ACCEPTED SOLUTION
Solution Sage

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 ) )```
4 REPLIES 4
Super User

Hi,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor
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.
Solution Sage

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
Not applicable

Thank you Matt! It works perfectly.

-lara

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.