Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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: 

YearMonthTotal Widgets3MMA
1995January39749.95883.33
1995February32164.81799.05
1995March27386.31735.56
1995April23995.52618.86
1995May29757.07601.02
1995June28136.16606.58
1995July23935.69606.14

 

"Sample" table:

 

DateSizeTypeNorth AmericaEuropeJapanAsia-PacificTaiwanOther Asia
Jan-95< = 4.5"Epi112.4142335207.15689822054.8601072592.63300
Jan-95< = 4.5"Polished117.8424424203.98141632131.0085452916.71200
Jan-95>= 6.51"Epi106.0821647209.29313161978.3789062303.82800
Jan-95>= 6.51"Polished100.4726338203.98141631932.4555662074.54800
Jan-954.51-5.5"Epi96.59112156207.61878651775.8037112076.75200
Jan-954.51-5.5"Polished93.44502151206.57953791668.9604492006.20400
Jan-955.51-6.5"Epi97.76392625199.53574151758.0749512175.9600
Jan-955.51-6.5"Polished97.10641057185.33267651783.6271972081.16100
Jan-95NANon-Polished94.09347357185.50588461655.0656742059.11500
Feb-95< = 4.5"Epi93.07671086216.51013611626.15212045.88700
Feb-95< = 4.5"Polished89.51329764238.16114971503.9101562010.61400
Feb-95>= 6.51"Epi85.65393566226.96035871430.6567381878.33700
Feb-95>= 6.51"Polished84.71681296224.88186141430.829591876.13200
Feb-954.51-5.5"Epi83.88587124213.62333431452.3811041803.37900
Feb-954.51-5.5"Polished83.98844577211.83351721442.9519041816.60700
Feb-955.51-6.5"Epi82.95270121207.50331451369.2346191823.22100
Feb-955.51-6.5"Polished80.47742747193.70440181296.995851746.05900
Feb-95NANon-Polished78.19089278183.65833151232.4067381699.76200
Mar-95< = 4.5"Epi77.09715521183.83153961175.3112791682.12500
Mar-95< = 4.5"Polished80.12592923185.96777291230.8288571787.94700
Mar-95>= 6.51"Epi77.45823218184.63984411168.9111331706.37600
Mar-95>= 6.51"Polished75.74860988189.54740721140.1816411664.48800
Mar-954.51-5.5"Epi74.26850785197.91913241081.8464361651.2600
Mar-954.51-5.5"Polished75.59330561197.11082791130.8842771655.6700
Mar-955.51-6.5"Epi76.21715322196.53346761113.2585451613.78200
Mar-955.51-6.5"Polished75.46466258174.13188551087.7062991596.14500
Mar-95NANon-Polished72.12837795164.20128721029.2365721512.36900
Apr-95< = 4.5"Epi70.28078157176.9609513996.20751951521.18800
Apr-95< = 4.5"Polished69.73296137193.0693054973.29418951530.00600
Apr-95>= 6.51"Epi64.89035172189.8360874918.84936521309.54400
Apr-95>= 6.51"Polished66.95429328183.0232351957.84790041439.61700
Apr-954.51-5.5"Epi66.64782224180.4828495958.86401371450.6400
Apr-954.51-5.5"Polished66.10299901191.1062802959.53198241424.18500
Apr-955.51-6.5"Epi66.08449868193.1270414951.48413091463.86800
Apr-955.51-6.5"Polished66.05670376182.965499965.14697271444.02600
Apr-95NANon-Polished66.90749256174.7092458987.38745121474.89100
May-95< = 4.5"Epi69.34204749176.96095131076.5307621574.09900
May-95< = 4.5"Polished73.87688108168.06960171230.4558111649.05500
May-95>= 6.51"Epi75.57017217163.50845481301.7292481598.3500
May-95>= 6.51"Polished78.21938756171.41829181363.5671391675.51100
May-954.51-5.5"Epi81.40060871169.51300261453.4340821765.90100
May-954.51-5.5"Polished80.61640635173.15037291465.5783691701.96700
May-955.51-6.5"Epi81.93053309176.41591311520.2099611704.17100
May-955.51-6.5"Polished83.25443391177.59697711603.667481640.23700
May-95NANon-Polished82.81556289174.34686591613.728761560.87100
Jun-95< = 4.5"Epi80.24953947170.82124991566.2580571435.20800
Jun-95< = 4.5"Polished78.28813773168.08708491516.7277831388.91100
Jun-95>= 6.51"Epi78.99799722165.73465391549.3391111415.36600
Jun-95>= 6.51"Polished77.43798463168.47079981548.906251375.68300
Jun-954.51-5.5"Epi76.05308569169.52997281491.1616211428.59300
Jun-954.51-5.5"Polished76.4787245171.74340591455.7629391501.34600
Jun-955.51-6.5"Epi75.30334423171.67299791370.3503421532.21100
Jun-955.51-6.5"Polished72.27812968171.00671581289.135011419.77500
Jun-95NANon-Polished71.04938972168.53280041275.0290531364.6600
Jul-95< = 4.5"Epi67.76015646162.19073371162.8061521331.59100
Jul-95< = 4.5"Polished67.14890774163.85372621136.0090331338.20400
Jul-95>= 6.51"Epi64.08946392164.78202421012.1699221294.11200
Jul-95>= 6.51"Polished63.46885977162.72808831027.6479491272.06600
Jul-954.51-5.5"Epi66.89670655160.37508081154.66041344.81800
Jul-954.51-5.5"Polished65.31062346156.25551621095.6960451320.56700
Jul-955.51-6.5"Epi65.3395327154.78877331075.8544921358.04600
Jul-955.51-6.5"Polished66.15478935160.200721097.9008791388.91100
Jul-95NANon-Polished66.46749596162.20910071095.2551271389.35200

 

Desired output:

 Monthly Total3MMA
Jan-9539749.95 
Feb-9532164.81 
Mar-9527386.3133100.36
Apr-9523995.5227848.88
May-9529757.0727046.3
Jun-9528136.1627296.25
Jul-9523935.6927276.31

 

1 ACCEPTED SOLUTION
mattbrice
Solution Sage
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 ) )

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Solution Sage
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.