DAX rolling monthly average

0
votes

I have a measure that uses a start and end date to calculate the total number of people employed in a month. I would like to use this measure to produce a rolling 12 month average. This is my measure: -

VAR _FirstDate = MIN ( 'Dates'[Date] )
VAR _LastDate = MAX ( 'Dates'[Date] )
VAR _Filter =
    FILTER (
        'Employment',
        'Employment'[Start] <= _LastDate
            && (
                ISBLANK ( 'Employment'[End] )
                    || 'Employment'[End] >= _FirstDate
            )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Employment'[id] ), _Filter )

If I matrix the measure using a date table with month/year in Power BI I would get this:

Month Year Employed
Jan-20 10
Feb-20 9
Mar-20 12
Apr-20 15
May-20 16
Jun-20 15
Jul-20 14
Aug-20 16
Sep-20 16
Oct-20 17
Nov-20 16
Dec-20 16

I cannot simply average the raw data as I need a snapshot of what the employed number was each month.
Is there a way in DAX to generate this table as a variable table and then average the Employed column, which in this example would give me an average of 14.

1
averagedaxrolling-computation

1 Answers

0
votes

I've managed to achieve what I needed using this DAX: -

VAR _FirstDate =
    FIRSTDATE ( STARTOFMONTH ( DATEADD ( Dates[Date], -1, YEAR ) ) )
VAR _LastDate =
    MAX ( Dates[Date] )
VAR _Total =
    AVERAGEX (
        SUMMARIZE (
            FILTER ( ALL ( Dates ), [Date] >= _FirstDate && [Date] <= _LastDate ),
            Dates[Month Year Name Short],
            "Total", [My Measure]
        ),
        [Total]
    )
RETURN
    _Total

I'm conscious that this may not be the most efficient way of achieving this so any suggestions are welcome.