rolling 3 months

[Anonymous]

I need help with setting up my DAX to only calculate the last 3 rolling average months.  This is what I have so far:

Rolling Avg Price = AVERAGEX(FILTER(ALLSELECTED(vwInvoices[Month]),

vwInvoices[Month] <= MAX(vwInvoices[Month])), CALCULATE(SUM(vwInvoices[Price])))

any help is greatly appreciated!

[amitchandak]

@Anonymous , With a date table, try a formula like

Rolling 3 = CALCULATE(AVERAGE(vwInvoices[Price]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(AVERAGE(vwInvoices[Price]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(AVERAGE(vwInvoices[Price]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Or sum them and divide by 3

or sum and divide by distinct months

Rolling 3 = CALCULATE(sum(vwInvoices[Price]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))/3

Rolling 3 = Divide( CALCULATE(sum(vwInvoices[Price]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH)) ,

CALCULATE(distinctcount(Date[Month Year]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH)))

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.