Need cumulative total for each BU

[snaraya]

Hi everyone,

need help to create cumulative total DAX column or even a Mcode  , but i dont need a measure 

where 2019-2022 should be blank/null

2023=2022

2024=2022+2023

2025=2022+2023+2024  and so on till 2030 for each BU

BU	Year	reduction	Index
ADD	2019		0
ADD	2020		1
ADD	2021		2
ADD	2022	-4.3	3
ADD	2023	-1.7	4
ADD	2024	-16	5
ADD	2025	7	6
ADD	2026	-2.6	7
ADD	2027	-2	8
ADD	2028	-5	9
ADD	2029	-10	10
ADD	2030	-2	11
CA	2019		12
CA	2020		13
CA	2021		14
CA	2022	-18	15
CA	2023	-16.81	16
CA	2024	-7.89	17
CA	2025	-18	18
CA	2026	-9.25	19
CA	2027	-2	20
CA	2028	-7.5	21
CA	2029	0	22
CA	2030	0	23

[amitchandak]

@snaraya , Create a separate year column and join with year

and try a column like

calculate(Sum(Table[reduction]), filter(all(year), Year[Year] <= max(Year[Year])   && Year[Year]>=2022) )

You can try window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[snaraya]

Hi @amitchandak ,

Thanks for reply,

i am confused a little now, i created a new table with Year and created a relationship between both table. and after using your formula in a column it gives me same column as reduction.

What am i missing here exactly 

also when i tried creating a cumulative using window function it gives circular dependency error

what exactly do you mean when you say join with year

Relationship

[snaraya · Accepted solution]

@amitchandak  I got the solution,

i created below column 

Cumulative Total = CALCULATE( SUM('Table'[reduction]), FILTER( ALLEXCEPT('Table', 'Table'[BU]), 'Table'[Year]<=EARLIER('Table'[Year])-1))