Rolling 13 Period Average of Hours

[L_Oram]

Afternoon All 🙂

I'd like to create a measure that would give me the rolling average hours from the past 13 months. What is the best way to go about it? sample data below

FYPeriod	Total Hours
202205	1333119.5
202206	1337301.75
202207	1362952.22
202208	1484211.8
202209	1483151.11
202210	1300528.75
202211	1393122
202212	1499369.5
202213	1427705.5
202301	1436508.5
202302	1308720.5
202303	1307649.5
202304	1310249.5
202305	1350095.5
202306	1411667.5
202307	1384331.5
202308	1398125.5
202309	1669522.5
202310	1353769.5
202311	1400674
202312	1361626
202313	1320563.5
202401	1227380
202402	1277859
202403	1358457
202404	1365336

[DataNinja777 · Accepted solution]

Hi @L_Oram ,

You can generate the desired output using a DAX measure like the one below:

Moving Average 13 Months = 
VAR CurrentPeriod = MAX('Table'[FYPeriod])
VAR Last13Periods =
    TOPN(
        13, 
        FILTER(
            ALL('Table'), 
            'Table'[FYPeriod] <= CurrentPeriod
        ),
        'Table'[FYPeriod], 
        DESC
    )
VAR Result = 
    CALCULATE(
        AVERAGEX(
            Last13Periods,
            'Table'[Total Hours]
        )
    )
RETURN
    Result

 This will produce the output like below:

Since your calendar has 13 periods and is non-standard, I didn't create a separate calendar table, although using a separate calendar table in the data model is considered best practice.

I have attached an example PBIX file.

[ThxAlot]

Running Avg WINDOW = 
AVERAGEX(
    WINDOW( -12, REL, 0, REL, ORDERBY( DATA[FYPeriod] ) ),
    CALCULATE( SUM( DATA[Total Hours] ) )
)

Running Avg TOPN = 
VAR __p =
    MAX( DATA[FYPeriod] )
RETURN
    AVERAGEX(
        TOPN(
            13,
            CALCULATETABLE( DATA, DATA[FYPeriod] <= __p ),
            DATA[FYPeriod], DESC
        ),
        DATA[Total Hours]
    )

[dharmendars007]

Hello @L_Oram , 

Thanks for your question..

Please find the DAX to be written to get the rolling Average 13 months without dates..You have to create the Index column starting 1 in Power Query to achive this

Rolling Average13 =

VAR _MaxIndex = MAX('Table'[Index])

VAR _MinIndex = _MaxIndex - 13

VAR _Filter13Months = FILTER(ALL('Table'),

                                'Table'[Index] <= _MaxIndex &&

                                'Table'[Index] > _MinIndex)

VAR _RollingAvg = AVERAGEX(_Filter13Months, 'Table'[Total Hours])

Return

_RollingAvg

If you find this helpful , please mark it as solution and give kudos
Thank You
Dharmendar S
LinkedIN