Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
snaraya
Helper II
Helper II

Need cumulative total for each BU

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

 

BUYearreductionIndex
ADD2019 0
ADD2020 1
ADD2021 2
ADD2022-4.33
ADD2023-1.74
ADD2024-165
ADD202576
ADD2026-2.67
ADD2027-28
ADD2028-59
ADD2029-1010
ADD2030-211
CA2019 12
CA2020 13
CA2021 14
CA2022-1815
CA2023-16.8116
CA2024-7.8917
CA2025-1818
CA2026-9.2519
CA2027-220
CA2028-7.521
CA2029022
CA2030023
1 ACCEPTED SOLUTION

@amitchandak  I got the solution,

 

i created below column Cumulative.PNG

 

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

Cumulative.PNGRelationshipRelationship

@amitchandak  I got the solution,

 

i created below column Cumulative.PNG

 

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.