cancel
Showing results for
Did you mean:
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:

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

Accepted Solutions
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
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 )
)
)```
Frequent Visitor

## Re: Moving Average for Prev 6/Next 6 Months

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

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.

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

"Proud to be a Datanaut!"
Power BI Blog

Announcements

#### Win Power BI Swag with Community Kudopalooza!

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 135 members 1,677 guests
Recent signins: