DAX Help: Cumulative Sum with Reset on Large Negative Values in Power BI.

[Powerananya]

I am working on a Power BI report and need help creating a DAX expression for a calculated column. My goal is to calculate a cumulative sum of sales values, but I want the sum to reset to zero whenever a large negative value causes the cumulative total to drop below zero. For example, if the cumulative sum is 92 and a subsequent value of -135 is encountered, the result should reset to zero instead of going negative

[bhanu_gautam · Accepted solution]

@Powerananya , 

Sort your data: Ensure your data is sorted by the date or the order in which you want to calculate the cumulative sum.

Create the calculated column:This expression will calculate the cumulative sum and reset it to zero whenever the cumulative total drops below zero.

DAX
CumulativeSales =
VAR CurrentRow = EARLIER('Sales'[RowID])
VAR CurrentValue = 'Sales'[SalesValue]
VAR PreviousCumulative =
CALCULATE(
SUM('Sales'[SalesValue]),
FILTER(
'Sales',
'Sales'[RowID] < CurrentRow
)
)
VAR NewCumulative = PreviousCumulative + CurrentValue
RETURN
IF(NewCumulative < 0, 0, NewCumulative)