topic Re: Rolling average and YTD for last 12 months in DAX Commands and Tips

Rolling average and YTD for last 12 months

snaraya — Thu, 27 Apr 2023 06:28:54 GMT

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

Re: Rolling average and YTD for last 12 months

wdx223_Daniel — Thu, 27 Apr 2023 08:25:24 GMT

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.

Re: Rolling average and YTD for last 12 months

snaraya — Thu, 27 Apr 2023 09:11:21 GMT

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

Re: Rolling average and YTD for last 12 months

wdx223_Daniel — Fri, 28 Apr 2023 00:48:19 GMT

here is the file for your reference

Re: Rolling average and YTD for last 12 months

snaraya — Wed, 03 May 2023 03:38:52 GMT

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-each-year/m-p/3215701#M117403