Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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 ) )
Hi,
You may refer to my solution here.
Hope this helps.
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 ) )
Thank you Matt! It works perfectly.
-lara
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |