Running Total but resetting if value is negative or zero

[czrnbstmnt]

I need to solve for the running total but it has to reset to 0 if the running total becomes negative or zero. On the left of the image below is the Excel formula for the RT which is basically =IF(current value+previous RT<0,0,current value+previous RT). The formula only differs for the first and second rows. The first row = Initial while the second row = Value.

I know that PBI cannot do recursion so I added a helper column named Reset Point (red column) which supposedly should tell me which row I should start my running total from without referring to a previous row in the RT column. For the Reset Point, I populated the column manually. But basically if the Reset Point is 2, the RT should start from Date Index=3.

I tried using the for loop equivalent in PBI to do this. I also tried using ChatGPT but I just can't figure out how to correct it. The codes below are what I used to calculate the Reset Point and RT columns.

I am not sure if this is the best approach to resetting the running total so please feel free to steer me in a completely different direction if needed. To be honest, I feel like I've made this much more complicated than it should be.

Also, if possible I want this done in PBI. Doing it in PQ is my last resort. Thank you to whoever will try to help. I've been trying to solve this for 2 weeks now. 🥲

RESET POINT

Reset Point = 

VAR __CurrentIndex = 'Table'[Date Index] // Get the current row's Date Index
VAR __CurrentCode = 'Table'[Code] // Get the current row's S-D-SKU value
//VAR __n = __CurrentIndex //MAXX(FILTER('Fact Table', 'Fact Table'[S-D-SKU] = __CurrentSKU), 'Fact Table'[Date Index]) // Max Date Index for current SKU
VAR __loopTable = GENERATESERIES(1, __CurrentIndex) // Generate a series to act as a loop

// Add the cumulative sum calculation within the loop
VAR __loopTable1 = 
    ADDCOLUMNS(
        __loopTable, 
        "__RT",
        VAR __LoopIndex = [Value] // The loop's current iteration value

        // Ensure that if Date Index = 1, PrevReset = 1 (for this SKU)
        VAR __PrevReset =
            IF(
                __LoopIndex = 1, 
                1, 
                MAXX(
                    FILTER(
                        'Table', 
                        'Table'[Date Index] = __LoopIndex - 1 &&
                        'Table'[Code] = __CurrentCode
                    ),
                    'Table'[Date Index]
                )
            )
        VAR __RunningTotal =
            IF(
                __LoopIndex = 1, 
                'Table'[Initial],
                SUMX(
                    FILTER(
                        'Fact Table',
                        'Fact Table'[Date Index] >= __PrevReset + 1 &&
                        'Fact Table'[Date Index] <= __LoopIndex &&
                        'Fact Table'[S-D-SKU] = __CurrentCode
                ),
                'Table'[Value]
                )
            )

        RETURN 
            IF(__RunningTotal <= 0, __LoopIndex, __PrevReset)
    )

// Get the last iteration value for the current Date Index and SKU
RETURN 
    MAXX(
        FILTER(__loopTable1, [Value] = __CurrentIndex), 
        [__RT]
    )

RUNNING TOTAL

RT = 
VAR CurrentCode = 'Table'[Code]  
VAR CurrentIndex = 'Table'[Date Index]  

-- For Date Index = 1
VAR Initial = 
    IF(CurrentIndex = 1, 'Table'[Initial], BLANK())

-- Retrieve the reset point from the new column
VAR ResetPoint = 'Table'[Reset Point]

-- Calculate adjusted total after reset
VAR AdjustedTotal = 
    IF(
        ISBLANK(ResetPoint), 
        SUMX(
            FILTER('Fact Table', 
                'Table'[Code] = CurrentCode && 
                'Table'[Date Index] <= CurrentIndex
            ), 
            'Table'[Value]
        ), 
        SUMX(
            FILTER('Table', 
                'Table'[Code] = CurrentCode && 
                'Table'[Date Index] > ResetPoint &&
                'Table'[Date Index] <= CurrentIndex
            ), 
            'Table'[Value]
        )
    )

-- Final Calculation
RETURN 
    IF(CurrentIndex = 1, Initial, MAX(0, AdjustedTotal)) 

[Deku]

Something like this. Group streaks > 0 as islands, then we can perform cumulative sum on the islands. Quick implementation and imagine could be cleaned up

cumSum = 
var rowNumber = SELECTEDVALUE( 'Table'[Date Index] )
var tbl =
	FILTER(
		ADDCOLUMNS(
			ALLSELECTED( 'Table'[Date Index], 'Table'[Value] )
			,"@RowNumber",
				RANK( DENSE, ALLSELECTED( 'Table'[Date Index] ), ORDERBY( 'Table'[Date Index], ASC ) )
			,"@Island", 
				RANK( DENSE, ALLSELECTED( 'Table'[Date Index] ), ORDERBY( 'Table'[Date Index], ASC ) ) -
				RANK( DENSE, 
					FILTER( ALLSELECTED( 'Table'[Date Index], 'Table'[Value] ), 'Table'[Value] > 0 ),
					ORDERBY( 'Table'[Date Index], ASC ) 
				) 
		)
		,'Table'[Value] > 0
	)
var island = SELECTCOLUMNS( FILTER( tbl, 'Table'[Date Index] = rowNumber ), "@selectedIsland", [@Island] )
RETURN
COALESCE(
    SUMX( FILTER( tbl, [@Island] in island && [@RowNumber] <= rowNumber ), 'Table'[Value] )
    ,0
)

[czrnbstmnt]

Hi, thank you for replying!

Your table shows the correct cumSum values but only until 11.

12 should not be a reset point because the reset is based on the cumulative sum, not the Value column. So for 12, the cumSum should be 750-100=650 which is more than 0. Hence, the next reset point should be 16 where cumSum=-40 (which should display 0).

[Deku]

Amended the approach I used in this blog post if you want more details

[lbendlin]

Conditional accumulations are impossible in DAX. Your only option for these is to use List.Accumulate in Power Query.

However your premise is not actually a conditional accumulation because of the way you define the reset points.

Why is 12 not a reset point?

[czrnbstmnt]

Hi, thanks for replying. 12 is not a reset point because the reset is based on RT, not the Value column. So for 12, the RT is 750-100=650 which is more than 0. Hence, the next reset point is 16 where RT=-40.

[lbendlin]

Not possible in DAX.

[Deku]

Based on this response my solution will not work. I don't think this can be done in DAX.

You can get the first reset point, but can't think of how to the get the others.