Resetting my cumulative total when it dips below zero

[zbrooks]

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

[v-sshirivolu]

Hi @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.

[zbrooks]

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.

[v-sshirivolu · Accepted solution]

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.

[v-sshirivolu]

Hi @zbrooks ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @zbrooks ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

[zbrooks]

Sample PowerBi 

I cant do this in SQL as this visual has slicers that filters it dynamically

[zbrooks]

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.

[FBergamaschi]

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

[zbrooks]

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

[zbrooks]

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

[zbrooks]

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