rolling 3 months

Anonymous — Thu, 10 Sep 2020 18:43:10 GMT

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!

Re: rolling 3 months

amitchandak — Tue, 03 Nov 2020 02:28:13 GMT

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.

topic rolling 3 months in DAX Commands and Tips

rolling 3 months

Re: rolling 3 months