Forum Discussion
How to do running total calculation with zero out negative value from last time period
Hey all, to make it easy I created a excel tabl here at below link.
All columns highligted in yellow are the raw data. I just added another two calculated columns there in blue, which I want to calculate in Power BI. Actually, I just want to calculate the ideal ending inventory, which is on hand + supply - demand. And then carry over currnet week ending inventory to next week as the beginning inventory, if current ending inventory is greater than 0. If it's negative, I will use 0 as the starting inventory in that week to repeat the calculation. Due to that approach, we will see the differnce. (see below example)
Item A:
Week: 4/30/2025
Week: 5/7/2025
Normal Calculation:
Ending Inventory in the week of 4/30 = Last week ending invnetory (16 in the week of 4/23/2025) - demands in the week of 4/30 (37) + supply in the week of 4/30 (20) = 16 - 37 +20 = -1
Ending Inventory in the week of 5/7 = Last week ending invnetory (-1 in the week of 4/30/2025) - demands in the week of 5/7 (14) + supply in the week of 5/7 (21) = -1-14+21 = 6
Ideal Calculation:
Ending Inventory in the week of 4/30 = Last week ending invnetory (16 in the week of 4/23/2025) - demands in the week of 4/30 (37) + supply in the week of 4/30 (20) = 16 - 37 +20 = -1
Since -1 is less than 0, so I will take 0 as the beginning inventory in the week of 5/7
Ending Inventory in the week of 5/7 = Last week ending invnetory (0 in the week of 4/30/2025) - demands in the week of 5/7 (14) + supply in the week of 5/7 (21) = 0-14+21 = 7
Would you let me know how I can achieve that calculation in Power BI? Thanks a lot!