Moving Average using Last and Next 3 months

[Anonymous]

I'm trying to calculate the moving average which looks at data for both the last 3 months and next 3 months.

Table of example data is below and the values i need are in column 3 (highlighted green). As an example for August 2016, the calculation would be to add the values of Column 2 for May, June, July, August, September, October and November and divide by 7 giving   43,665.04

Example Data

month of Notification Date	Avg. Total Income	Moving Average of Avg. Total Income
January 2016	17,710.41	39,391.21
February 2016	8,289.12	51,280.18
March 2016	69,765.35	53,912.75
April 2016	61,799.95	47,576.68
May 2016	98,836.09	49,924.10
June 2016	67,075.56	56,002.75
July 2016	9,560.25	51,980.03
August 2016	34,142.35	43,665.04
September 2016	50,839.68	42,494.24
October 2016	41,606.34	46,021.59
November 2016	3,594.99	51,944.28
December 2016	90,640.50	47,066.81

I currently am using the following calculation for moving averages but it only looks at the past  6 months:

_6m moving Total Income Average =

VAR period = 6

VAR the_first_date =

CALCULATE ( FIRSTDATE ( DateTable[Date] ), ALLSELECTED ( DateTable[Date] ))

VAR last_date =

LASTDATE ( DateTable[Date] )

VAR sum_in_period =

CALCULATE (

SUM ( Tabl1[totalIncome] ), DATESINPERIOD ( DateTable[Date], last_date, - period, MONTH ))

RETURN

IF ( last_date - the_first_date >= period -1 , sum_in_period / period )

[Anonymous]

@amitchandak  thanks i had to play around with the number of months to subtract but otherwise worked great

[amitchandak · Accepted solution]

@Anonymous , Try measure like this with date table

Rolling 3 before 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAXX('Date',dateadd('Date'[Date],-3,month)),7,MONTH))/7

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.