Rolling average and YTD for last 12 months

[snaraya]

Hi everyone,

i have below data 

where the Result column is summation of all values available for that year-month & BU, its a measure which uses SUM function

now for each BU i need to create a dynamic rolling 12 month and YTD( will have slicer for BU in report) ,

for rolling data: 

so here for year 2021 , the rolling 12 month values will be empty , from 2022 the value should be average of last 12month i.e

 for Jan 2022 BU A= (sum of result from 2022 Jan-Feb2021)/12

for Feb 2022 BA A= (sum of result from 2022 Feb-March 2021)/12

similarly for each month and each BU

for YTD: 

the summation value will start fom year 2021.

for Jan 2021 BU A = (result Jan)/12

for Feb 2021 BU A = sum(result Jan2021+result feb 2021) /12 and so on till dec

for 2022 

for Jan 2022 BU A = (result Jan)/12

for Feb 2022 BU A = sum(result Jan 2022+result feb 2022) /12 similarly for each month till december for that year 

Reporting Period	BU	Month	Month_number	Result
2021	A	January	1	2949
2021	B	January	1	2304
2021	A	February	2	2988
2021	B	February	2	2370
2021	A	March	3	2990
2021	B	March	3	2372
2021	A	April	4	2989
2021	B	April	4	2390
2021	A	May	5	2989
2021	B	May	5	2390
2021	A	June	6	2989
2021	B	June	6	2390
2021	A	July	7	2979
2021	B	July	7	2365
2021	A	August	8	3003
2021	B	August	8	2376
2021	A	September	9	3003
2021	B	September	9	2376
2021	A	October	10	3003
2021	B	October	10	2424
2021	A	November	11	3003
2021	B	November	11	2424
2021	A	December	12	3003
2021	B	December	12	2424
2022	A	January	1	3113
2022	B	January	1	2403
2022	A	February	2	3113
2022	B	February	2	2403
2022	A	March	3	3113
2022	B	March	3	2403
2022	A	April	4	3109
2022	B	April	4	2403
2022	A	May	5	3109
2022	B	May	5	2403
2022	A	June	6	3110
2022	B	June	6	2510
2022	A	July	7	3110
2022	B	July	7	2510
2022	A	August	8	3110
2022	B	August	8	2510
2022	A	September	9	3110
2022	B	September	9	2510
2022	A	October	10	3215
2022	B	October	10	2376
2022	A	November	11	3290
2022	B	November	11	2382
2022	A	December	12	3292
2022	B	December	12	2382
2023	A	January	1	3499
2023	B	January	1	2473
2023	A	February	2	3362
2023	B	February	2	2458
2023	A	March	3	3353
2023	B	March	3	2459

i tried multple formulas but the output was not correct which i need.

any help is appreciated

[wdx223_Daniel]

Rolling Avg Last 12 months =
VAR _tbl =
    TOPN (
        12,
        WINDOW (
            -11,
            REL,
            0,
            REL,
            ALL ( Data[Reporting Period], Data[Month_number] ),
            ORDERBY ( Data[Reporting Period], ASC, Data[Month_number], ASC )
        ),
        data[reporting period],,
        data[month_number],
    )
RETURN
    IF (
        MAX ( Data[Reporting Period] ) > 2021,
        AVERAGEX (
            _tbl,
            CALCULATE ( SUM ( Data[ Result] ), ALLSELECTED ( Data[Month] ) )
        )
    )

Rolling Avg Last 12 months YTD =
VAR _tbl =
    WINDOW (
        -11,
        REL,
        0,
        REL,
        ALL ( Data[Reporting Period], Data[Month_number] ),
        ORDERBY ( Data[Reporting Period], ASC, Data[Month_number], ASC ),
        ,
        PARTITIONBY ( Data[Reporting Period] )
    )
RETURN
    AVERAGEX (
        _tbl,
        CALCULATE ( SUM ( Data[ Result] ), ALLSELECTED ( Data[Month] ) )
    )

it is not what you really want. but maybe it gives you some tips.

[snaraya]

Rolling Avg Last 12 months values are correct in the screenshot...thanks for that

but when i tried to use your formula i am little confused after orderby line which seems to be like meausre are used, so am i supposed to create a measure for reporting period and month_number??

is it possible for you to share the pbix file?

     ORDERBY ( Data[Reporting Period], ASC, Data[Month_number], ASC )
        ),
        data[reporting period],,
        data[month_number],

i will brainstorm wrt YTD more. as the output for BU A, for year 2021 are 246-Jan, 495-Feb, 744-March and so on ... will search more about that 

[wdx223_Daniel · Accepted solution]

here is the file for your reference

[snaraya]

Thankyou for the rolling average formula..It helped

for YTD cumulative i used below formula

Cumulative = 

CALCULATE (

    SUM('Table'[Result]),

    FILTER ( ALL ( 'Table'[Month_number] ), 'Table[Month_number] <= MAX ( 'Table'[Month_number] ) )

)/12

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Sum-previous-months-result-to-next-month-for-...