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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
njd763
Regular Visitor

Cumulative Total

I am looking to calculate a cumulative total of a column based on another column by sequential dates. There are orders for multiple years in the same table. The formula I currently have is making it so it calculates a cumulative total starting with the first date all the way up through the last date in our list, spanning 5 years. How do I make it go year by year?

4 REPLIES 4
deevaker
Resolver I
Resolver I

Hi @njd763,

Are you looking at something like below ? if yes, then you can use this measure :

Cumulative Total by year = SUMX(FILTER(ALL('Table'),year('Table'[date])<=YEAR(MIN('Table'[date])) ),[value])

deevaker_0-1666123002572.png

 

Thanks,

Deevaker
+91-9711975011
deevaker@hotmail.com

https://www.linkedin.com/in/deevakerg/

 

v-xiaotang
Community Support
Community Support

Hi @njd763 

Thanks for reaching out to us.

please try this measure

Cumulative Total by year = SUMX(FILTER(ALL('Table'),year('Table'[date])=YEAR(MIN('Table'[date])) && 'Table'[date]<= MIN('Table'[date])),[value])

vxiaotang_0-1665654785437.png

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Shaurya
Memorable Member
Memorable Member

Hi @njd763,

 

Let's assume you already have a measure for calculating cumulative total. You can use the this formula to make it go year by year instead of all data:

 

Cumulative Year by Year = CALCULATE([Total],DATESYTD('Date'[Date],"12/31"))

 

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

 

@Shaurya - Would this only show for 1 year? 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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