Sum previous months result to next month for each year

[snaraya]

Hi everyone,

I want to sum result column monthly and divide it by 12, and which resets yearly

such as Jan 2021 =jan sum_result 2021 /12

Feb=( jan+feb)/12 and so on for each month and each BU

and when it starts 2022 it should start again as 

Jan 2022=jan sum_result 2022/12

Feb 2022 =( jan+feb)/12 and so on for each month and each BU

I was not able to do this directly so i thought to create new measure Divide= [Sum_result]/12 and just do cumulative sum monthly for each year of a measure but couldnt do that too

result is a column where sum fuction is used in fields pane,

i dont have a date column but i have a month and year column so i created a date column using that where every date is 1st of each month 

Date = FORMAT('Table'[Month] &"-"& 'Table'[Reporting Period],"MM-YYYY") and changed it into date format

using this i created a date table : calender = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])) and then linked relationship between two tables

Below is the data 

i tried to create a new measure called cumulative with below fomula so that it just do a cumulative sum but it gave me same answer as divide column

Test = CALCULATE(SUMX('Table',[Divide]),FILTER(ALLSELECTED(calender[Date]),calender[Date]<=MAX(calender[Date])))

can someone guide what needs to be done exactly ..i tried multiple things

Reporting Period	Month_number	New BU	result	Divide	Cumulative	Required output
2021	1	A	2949	245.75	245.75	245.75
2021	2	A	2988	249	249	494.75
2021	3	A	2990	249.1667	249.1667	743.9167
2021	4	A	2989	249.0833	249.0833	993
2021	5	A	2989	249.0833	249.0833	1242.083
2021	6	A	2989	249.0833	249.0833	1491.167
2021	7	A	2979	248.25	248.25	1739.417
2021	8	A	3003	250.25	250.25	1989.667
2021	9	A	3003	250.25	250.25	2239.917
2021	10	A	3003	250.25	250.25	2490.167
2021	11	A	3003	250.25	250.25	2740.417
2021	12	A	3003	250.25	250.25	2990.667
2022	1	A	3113	259.4167	259.4167	259.4167
2022	2	A	3113	259.4167	259.4167	518.8333
2022	3	A	3113	259.4167	259.4167	778.25
2022	4	A	3109	259.0833	259.0833	1037.333
2022	5	A	3109	259.0833	259.0833	1296.417
2022	6	A	3110	259.1667	259.1667	1555.583
2022	7	A	3110	259.1667	259.1667	1814.75
2022	8	A	3110	259.1667	259.1667	2073.917
2022	9	A	3110	259.1667	259.1667	2333.083
2022	10	A	3215	267.9167	267.9167	2601
2022	11	A	3290	274.1667	274.1667	2875.167
2022	12	A	3292	274.3333	274.3333	3149.5
2023	1	A	3499	291.5833	291.5833	291.5833
2023	2	A	3362	280.1667	280.1667	571.75
2023	3	A	3353	279.4167	279.4167	851.1667

any help appreciated

[Mahesh0016 · Accepted solution]

@snaraya 

Cumulative =

CALCULATE (

    [Total Result],

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

)
###########################################

Required output = DIVIDE([Cumulative],12,0)

###########################################

Total Result = SUM('Sum'[result])

Thank You!!

[snaraya]

Thankyou so much for this..Saved me!!😊