Reply
Frequent Visitor
Posts: 12
Registered: ‎04-10-2017
Accepted Solution

Moving Average for Prev 6/Next 6 Months

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: 

YearMonthSales
2012January125421.9
2012February97483.81
2012March103970.4
2012April116290.7
2012May162398.2
2012June142223.1
2012July162353.6
2012August142585.7
2012September202582.3
2012October181586.7
2012November532586.6
2012December284846
2011January126987.9
2011February87950.17
2011March65068.93
2011April75960.54
2011May144368.8
2011June120091.5
2011July121481
2011August117987.7
2011September285433.4
2011October260563.5
2011November326864
2011December195801.1

Accepted Solutions
Member
Posts: 98
Registered: ‎03-07-2017

Re: Moving Average for Prev 6/Next 6 Months

[ Edited ]

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

View solution in original post


All Replies
Member
Posts: 98
Registered: ‎03-07-2017

Re: Moving Average for Prev 6/Next 6 Months

 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 )
    )
)
Frequent Visitor
Posts: 12
Registered: ‎04-10-2017

Re: Moving Average for Prev 6/Next 6 Months

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: 

 

MA1.JPG

 

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

MA2.JPGMA3.JPG 

 

 

 

Here's full data set: 

 

YearMonthSales
2010January139730.5
2010February130534.1
2010March145615.7
2010April130543
2010May93938.81
2010June128304.3
2010July135036.5
2010August195567.4
2010September126080.9
2010October259289
2010November212633.3
2010December227059.5
2011January115144.9
2011February97745.88
2011March59156.74
2011April74669.67
2011May146157
2011June116518.6
2011July116243.5
2011August118415.9
2011September311721.7
2011October245237.7
2011November323051.3
2011December220444.6
2012January96276.63
2012February95216.72
2012March102573.8
2012April129869.1
2012May152760.3
2012June145649.7
2012July158229
2012August150146.1
2012September199804.2
2012October199429.5
2012November520100.4
2012December280675.8
2013January215229.2
2013February149129
2013March171791
2013April143738.8
2013May218862.2
2013June155990.5
2013July166914.9
2013August288185.1
2013September267567.2
2013October378212
2013November375129.2
2013December321610.8
Member
Posts: 98
Registered: ‎03-07-2017

Re: Moving Average for Prev 6/Next 6 Months

[ Edited ]

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

Frequent Visitor
Posts: 12
Registered: ‎04-10-2017

Re: Moving Average for Prev 6/Next 6 Months

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. Smiley Happy

 

MA4.JPG

Super User
Posts: 2,938
Registered: ‎06-24-2015

Re: Moving Average for Prev 6/Next 6 Months

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

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"