Calculating Cumulative Inventory with a Conditional "Reset to Zero" if Previous Month is Negative

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