cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smp0150 Frequent Visitor
Frequent Visitor

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reid_Havens Member
Member

Re: Moving Average for Prev 6/Next 6 Months

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

5 REPLIES 5
Reid_Havens Member
Member

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 )
    )
)
smp0150 Frequent Visitor
Frequent Visitor

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
Reid_Havens Member
Member

Re: Moving Average for Prev 6/Next 6 Months

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 Frequent Visitor
Frequent Visitor

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
Super User

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!"
Power BI Blog

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 135 members 1,677 guests
Please welcome our newest community members: