Forum Discussion
Calculating Cumulative Inventory with a Conditional "Reset to Zero" if Previous Month is Negative
I am building an Inventory Projection report in Power BI. I need to calculate the Opening Inventory for each month based on a starting balance from February 2026, then adding/subtracting monthly movements (Purchase Orders vs. Forecasts).
The core challenge is the reset logic required for planning:
Standard Accumulation: If the balance is positive, it should carry over to the next month as the Opening Inventory.
Conditional Reset: If the calculated balance for a month is negative (e.g., April ends at -51), I still want to show that negative value for that specific month.
The "Planning Start" Rule: However, for the next month's calculation (e.g., May), the calculation should not start from -51. Instead, it should reset to 0 and then apply that month's movements (PO - Forecast).
Current Conflict:
A standard cumulative DAX measure carries the negative debt forward (e.g., $-51 + (-346) = -397$), which is logically correct for accounting but incorrect for my supply planning needs.
Attempts to use MAX(0, ...) often hide the negative values I need to see in the current month or cause "Circular Dependency" errors when trying to reference the measure's own previous value.
Data Structure:
DateTable: Standard calendar table.
Opening Balance: A static value from a history table for the starting month (Feb 2026).
Measures: [IN_PO], [Received Qty PO], and [Item Forecast].
Example of Desired Output:
| Month | Calculation Logic | Opening Inventory (Result) |
| Feb | Starting Balance | 428 |
| Mar | $428 + 268 - 405$ | 86 |
| Apr | $86 + 0 - 346$ | -51 (Show negative) |
| May | Reset to 0 because April was negative | -261 (0 + May movements) |
Question: How can I write a DAX measure that iterates through the months and resets the "Internal Carryover" to zero without losing the visibility of the negative result in the previous month's row?
Hello MuradMusleh_YE,
A standard running total in DAX will carry negative balances forward because it simply accumulates prior periods. That behavior is correct for accounting scenarios, but your requirement is a planning rule where:
The month can display a negative balance
The next month must start from 0 if the previous balance was negative
Conceptually, this becomes a sequential calculation:
VisibleBalance(n) = Carryover(n-1) + Movement(n) Carryover(n) = MAX(0, VisibleBalance(n))Because each month depends on the previous month’s computed result, this is a state-based calculation rather than a simple cumulative aggregation.
While it is possible to simulate this in DAX using complex iterator patterns (for example, SUMX with an indexed month column), those solutions can become difficult to maintain and may not scale well.
Recommended approach
For a clean and robust implementation:
Perform the month-by-month reset logic in Power Query (M), SQL, or a Dataflow
Materialize the projected balance as a column
Use DAX only for aggregation and reporting
This guarantees:
Negative months remain visible
The following month resets to 0
No circular dependency
Stable performance at scale
3 Replies
Hello MuradMusleh_YE,
A standard running total in DAX will carry negative balances forward because it simply accumulates prior periods. That behavior is correct for accounting scenarios, but your requirement is a planning rule where:
The month can display a negative balance
The next month must start from 0 if the previous balance was negative
Conceptually, this becomes a sequential calculation:
VisibleBalance(n) = Carryover(n-1) + Movement(n) Carryover(n) = MAX(0, VisibleBalance(n))Because each month depends on the previous month’s computed result, this is a state-based calculation rather than a simple cumulative aggregation.
While it is possible to simulate this in DAX using complex iterator patterns (for example, SUMX with an indexed month column), those solutions can become difficult to maintain and may not scale well.
Recommended approach
For a clean and robust implementation:
Perform the month-by-month reset logic in Power Query (M), SQL, or a Dataflow
Materialize the projected balance as a column
Use DAX only for aggregation and reporting
This guarantees:
Negative months remain visible
The following month resets to 0
No circular dependency
Stable performance at scale
Hi MuradMusleh_YE,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Olufemi7 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi @MuradMusleh_YE,
We wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
