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[Month] <= MAX(vwInvoices[Month])), CALCULATE(SUM(vwInvoices[Price])))
 
any help is greatly appreciated!

1 Reply

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