Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

rolling 3 months

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...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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.