Forum Discussion
Resetting my cumulative total when it dips below zero
Hi
I have a cumulative sum that has a starting total which then needs to be adding a Loadingtotal. this loading total could be positive or negative. if the cumulitive sum goes below zero it should reset and start adding the loading total as if it was starting again but at zero. I have the following as an example
Any help would be appreciated
Hi zbrooks ,
This approach is built in Power Query, so the calculation is done during data load. Because of that, it won’t respond to slicers or filters applied in the report the values will remain static regardless of user interaction.
If you need the cumulative total to change dynamically based on slicers, then the same logic would need to be implemented using a DAX measure instead.
Hope this helps.
Thank you.
12 Replies
- FBergamaschi
Super User
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Date LoadingTotal StartTotal Cumulative_Incorrect Cumulative_Correct 2026/04/01 2560 4765 7324 7324 2026/04/02 -1140 0 6184 6184 2026/04/03 -2847 0 3337 3337 2026/04/04 -2291 0 1046 1046 2026/04/05 -1723 0 -677 0 2026/04/06 -1707 0 -2384 0 2026/04/07 1725 0 -659 1725 2026/04/08 -417 0 -1076 1308 2026/04/09 -416 0 -1492 892 2026/04/10 -74 0 -1566 818 2026/04/11 -1823 0 -3389 0 2026/04/12 -1321 0 -4710 0 2026/04/13 -207 0 -4917 0 2026/04/14 0 0 -4917 0 2026/04/15 0 0 -4917 0 2026/04/16 0 0 -4917 0 2026/04/17 0 0 -4917 0 2026/04/18 0 0 -4917 0 2026/04/19 5000 0 83 5000 2026/04/20 0 0 83 5000 2026/04/21 -714 0 -631 4286 2026/04/22 -714 0 -1345 3571 2026/04/23 -714 0 -2060 2857 2026/04/24 -714 0 -2774 2143 2026/04/25 -714 0 -3488 1429 2026/04/26 -714 0 -4203 714 2026/04/27 -114 0 -4317 600 2026/04/28 14507 0 10191 15107 2026/04/29 0 0 10191 15107 2026/04/30 -1453 0 8737 13654 Date LoadingTotal StartTotal Cumulative_Incorrect Cumulative_Correct 2026/04/01 2560 4765 7324 7324 2026/04/02 -1140 0 6184 6184 2026/04/03 -2847 0 3337 3337 2026/04/04 -2291 0 1046 1046 2026/04/05 -1723 0 -677 0 2026/04/06 -1707 0 -2384 0 2026/04/07 1725 0 -659 1725 2026/04/08 -417 0 -1076 1308 2026/04/09 -416 0 -1492 892 2026/04/10 -74 0 -1566 818 2026/04/11 -1823 0 -3389 0 2026/04/12 -1321 0 -4710 0 2026/04/13 -207 0 -4917 0 2026/04/14 0 0 -4917 0 2026/04/15 0 0 -4917 0 2026/04/16 0 0 -4917 0 2026/04/17 0 0 -4917 0 2026/04/18 0 0 -4917 0 2026/04/19 5000 0 83 5000 2026/04/20 0 0 83 5000 2026/04/21 -714 0 -631 4286 2026/04/22 -714 0 -1345 3571 2026/04/23 -714 0 -2060 2857 2026/04/24 -714 0 -2774 2143 2026/04/25 -714 0 -3488 1429 2026/04/26 -714 0 -4203 714 2026/04/27 -114 0 -4317 600 2026/04/28 14507 0 10191 15107 2026/04/29 0 0 10191 15107 2026/04/30 -1453 0 8737 13654 - FBergamaschi
Hi zbrooks
is the table you provided me a pure dataset or includes some measure results? I mean: please send me only the raw columns you have and the code of the measures you have already implemented. So I can start from there
Anyway it looks like a recursive calculation at first sight, so better to be done in SQLenvironments but I might be wrong.
Thanks
Date LoadingTotal StartTotal Cumulative_Incorrect Cumulative_Correct 2026/04/01 2560 4765 7324 7324 2026/04/02 -1140 0 6184 6184 2026/04/03 -2847 0 3337 3337 2026/04/04 -2291 0 1046 1046 2026/04/05 -1723 0 -677 0 2026/04/06 -1707 0 -2384 0 2026/04/07 1725 0 -659 1725 2026/04/08 -417 0 -1076 1308 2026/04/09 -416 0 -1492 892 2026/04/10 -74 0 -1566 818 2026/04/11 -1823 0 -3389 0 2026/04/12 -1321 0 -4710 0 2026/04/13 -207 0 -4917 0 2026/04/14 0 0 -4917 0 2026/04/15 0 0 -4917 0 2026/04/16 0 0 -4917 0 2026/04/17 0 0 -4917 0 2026/04/18 0 0 -4917 0 2026/04/19 5000 0 83 5000 2026/04/20 0 0 83 5000 2026/04/21 -714 0 -631 4286 2026/04/22 -714 0 -1345 3571 2026/04/23 -714 0 -2060 2857 2026/04/24 -714 0 -2774 2143 2026/04/25 -714 0 -3488 1429 2026/04/26 -714 0 -4203 714 2026/04/27 -114 0 -4317 600 2026/04/28 14507 0 10191 15107 2026/04/29 0 0 10191 15107 2026/04/30 -1453 0 8737 13654
I will prepare a sample Pbix file with measures that i currently use. was just hoping someone has come accross a similiar issue and could point me in the right direction.
Sample PowerBi
I cant do this in SQL as this visual has slicers that filters it dynamicallyHi zbrooks ,
I was able to reproduce this from my end using the same sample data. The issue occurs due to how the cumulative total is evaluated row by row, especially when the running value goes below zero.To handle this correctly, I created a duplicate query and implemented the running total using a buffered list with List.Accumulate,, resetting the value to 0 whenever the cumulative result becomes negative. This avoids cyclic reference errors and ensures the calculation remains stable across all rows.
You can refer to the query in the attached PBIX file , where the cumulative logic is applied and produces the expected output.
Thank You.Thanks v-sshirivolu
A quick question, will this work if i have slicers on the page?
Truly appreciate the solution. Going to have learn some power query for the future.Hi zbrooks ,
This approach is built in Power Query, so the calculation is done during data load. Because of that, it won’t respond to slicers or filters applied in the report the values will remain static regardless of user interaction.
If you need the cumulative total to change dynamically based on slicers, then the same logic would need to be implemented using a DAX measure instead.
Hope this helps.
Thank you.
