Moving Average for Prev 6/Next 6 Months

[smp0150]

I need help calculating the Moving Average including previous 6 months and 6 months after. I'm assuming for finding the Moving Average based on the past 6 months the calculation is something similar to: 

Moving Average = CALCULATE(
AVERAGEX(
Orders, Orders[Sales]),
DATESINPERIOD(Orders[Ship Date],
LASTDATE(Orders[Ship Date]),
-6,
MONTH)
)

How do I update the formula to doing the Moving Average on the past 6 months and the next 6 months? (In Tableau it's considered a Moving Calculation, MicroStrategy it's an OLAP Avg function). Thanks! 

Data: 

Year	Month	Sales
2012	January	125421.9
2012	February	97483.81
2012	March	103970.4
2012	April	116290.7
2012	May	162398.2
2012	June	142223.1
2012	July	162353.6
2012	August	142585.7
2012	September	202582.3
2012	October	181586.7
2012	November	532586.6
2012	December	284846
2011	January	126987.9
2011	February	87950.17
2011	March	65068.93
2011	April	75960.54
2011	May	144368.8
2011	June	120091.5
2011	July	121481
2011	August	117987.7
2011	September	285433.4
2011	October	260563.5
2011	November	326864
2011	December	195801.1

[Reid_Havens]

 Hi there,

Happy to help. So if I'm understanding this correctly you're looking for a 12 month moving average "window" based on the LASTDATE(Orders[Ship Date]) correct? A window that is six months in the past from this date AND six months into the future. If that's the case the forumula would look like something below. Let me know if it works for you.

Moving Average =
CALCULATE (
    AVERAGE ( Order[Sales] ),
    DATESBETWEEN (
        'Date Table'[Date],
        DATEADD ( LASTDATE ( Order[ShipDate] ), -6, MONTH ),
        DATEADD ( LASTDATE ( Order[ShipDate] ), 6, MONTH )
    )
)

[smp0150]

Hi @Reid_Havens 

Thanks for your help. FYI I had a typo and need to leverage Order Date, not Ship Date. Also I don't have a date table - do I need one? When I do the calculation this is my formula for the new measure: 

Moving Average =
CALCULATE (	
AVERAGE ( Orders[Sales]),
	DATESBETWEEN ( 
		Orders[Order Date], 
		DATEADD ( LASTDATE ( Orders[Order Date] ), -6, MONTH ), 
		DATEADD ( LASTDATE ( Orders[Order Date] ), 6, MONTH )
	) 
) 

However the value looks really low for the Moving Average: 

Proofing out in Tableau and MicroStrategy Visual Insight/Desktop below where quick table calculations or derived metrics have formula boxes for calculations. 

 

Here's full data set: 

Year	Month	Sales
2010	January	139730.5
2010	February	130534.1
2010	March	145615.7
2010	April	130543
2010	May	93938.81
2010	June	128304.3
2010	July	135036.5
2010	August	195567.4
2010	September	126080.9
2010	October	259289
2010	November	212633.3
2010	December	227059.5
2011	January	115144.9
2011	February	97745.88
2011	March	59156.74
2011	April	74669.67
2011	May	146157
2011	June	116518.6
2011	July	116243.5
2011	August	118415.9
2011	September	311721.7
2011	October	245237.7
2011	November	323051.3
2011	December	220444.6
2012	January	96276.63
2012	February	95216.72
2012	March	102573.8
2012	April	129869.1
2012	May	152760.3
2012	June	145649.7
2012	July	158229
2012	August	150146.1
2012	September	199804.2
2012	October	199429.5
2012	November	520100.4
2012	December	280675.8
2013	January	215229.2
2013	February	149129
2013	March	171791
2013	April	143738.8
2013	May	218862.2
2013	June	155990.5
2013	July	166914.9
2013	August	288185.1
2013	September	267567.2
2013	October	378212
2013	November	375129.2
2013	December	321610.8

[Reid_Havens · Accepted solution]

The chart helped me a lot in knowing what you'd need. Ok so what you'll actually want is three measures: a 12 month SUM, a 12 month count to divide by, and the division measure. The reason we have a 12 month count is that we won't always be dividing by 12, sometimes we'll be dividing by less when we approach the beginning or end of the date range. I also realized doing a -6 AND +6 Month window does actually cause a 13 month window, since they're BOTH including the current month, so it's up to to you decide if the + or - should be 5 instead of 6, which will return a 12 month max rolling window.

12 Month Sum:

12M Sum = 
CALCULATE (	
SUM(  FactOrderProduct[Net Profit]),
	DATESBETWEEN ( 
		'Date Table'[Date], 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), -5, MONTH ), 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), 6, MONTH )
	) 
) 

12 Month Count

12M Count = CALCULATE (
    CALCULATE ( COUNTROWS ( VALUES ( 'Date Table'[Month  Year] ) ), FactOrderProduct ),
    DATESBETWEEN ( 
		'Date Table'[Date], 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), -5, MONTH ), 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), 6, MONTH )
	)
)

12 Month Average:

12M Average = DIVIDE([12M Sum], [12M Count], 0)

More info can be found here, I modified the query the SQLBI guys showcase in this post. You can try it without the DateTable, not 100% sure it'd work without it. But it's always a good practice to have one, since many functions in DAX require contiguous dates to work correctly.

Regards,

Reid

[smp0150]

Hi @Reid_Havens, 

I did some homework and for time intelligence functions, date dim tables are mandatory. So I created a simple date dimension by creating a new table ->Date = CALENDAR (DATE(2010,1,1), DATE(2013,12,31)). Also created a column for Month -> Mon-Year = FORMAT('Date'[Date], "MMMM YYYY")

As far as the formula itself for the Moving Average, I had issues with DATEADD for future end date. It would only calculate for 30 days out, so it would drop values for months that had 31 days, etc. Therefore I only used DATEADD for the beginning date, and EODMONTH for end date. Here's the final formulas: 

For the Denominator on the Moving Average: 

Month Count = CALCULATE(CALCULATE(COUNTROWS(VALUES('Date'[Mon-Year])), Orders), DATESBETWEEN('Date'[Date], Dateadd(FIRSTDATE(Orders[Order Date]), -6, MONTH), EOMONTH(FIRSTDATE(Orders[Order Date]), 6)))

For the Moving Average itself: 

Moving Average = DIVIDE(CALCULATE(SUM(Orders[Sales]), DATESBETWEEN('Date'[Date], Dateadd(FIRSTDATE(Orders[Order Date]), -6, MONTH), EOMONTH(FIRSTDATE(Orders[Order Date]), 6))), [Month Count])

Final output below. Thank you so much for your help!! You got me in exactly in the right place. Thanks again!! 

PS - I hope that in the future Power BI makes these functions as intutive as MSTR and Tableau, instead of having to build so many components for one calculation. 🙂

[GilbertQ]

Hi @smp0150

In the latest version of the Power BI Desktop (May 2017) they actually have created a Quick measure for the Moving Average which is exactly what you were trying to achieve.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-feature-summary/#quickMeasures