Rolling Average last 3, 6, 12 months

[JL2007]

Hi!

I'm trying to do something I feel like should be simple, I'd like to find the rolling average of our bookings for the last 3, 6 and 12 months. I imagine it would be something along this but I don't know what date formula to use in order to look back the 3, 6, and 12 months. any help would be appreciated!

6M Bookings HR =

CALCULATE (

    SUM ( 'Bookings'[Contract Direct Labor] ), 'Table of Dates'[Month]) - 6Months/6

[danextian]

Hi @JL2007 

SQLBI actually has a very good explanation on how to achieve this using DATESINPERIOD and AVERAGEX.

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ 

[Ashish_Mathur]

Hi,

Assuming Bookings is a measure that you have already written, write this measure

Rolling 3 months average booking = averagex(Summarize(calculatetable(calendar,datesbetween(calendar[date],edate(min(calendar[date]),-2),max(calendar[date]))),calendar[year],calendar[month name],"A",[bookings]),[A])

To your visual, ensure that you drag Year and Month name from the Calendar table.

Hope this helps.

[techies]

Hi @JL2007 please try this

3M_Bookings_HR =
CALCULATE(
AVERAGEX(
VALUES('Date'[Month Number]),
SUM(Bookings[Contract Direct Labor])
),
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-3,
MONTH
)
)

[JL2007]

Thanks, it's giving me the total for the last 3 months, and not the average, I'd expect it to be $7,079,700 as the average not $21,239,101 for February

[techies · Accepted solution]

try this @JL2007 

measure =

CALCULATE(

    AVERAGEX(

        SUMMARIZE('Date', 'Date'[Year], 'DATE'[month number]),

        CALCULATE(SUM(Bookings[Contract Direct Labor]))

    ),

    DATESINPERIOD(

        'Date'[Date],

        MAX('Date'[Date]),

        -3,

        MONTH

    )

)