Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
czrnbstmnt
New Member

Running Total but resetting if value is negative or zero

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.

 

RTreset.png

 

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)) 

 

7 REPLIES 7
Deku
Super User
Super User

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
)

 

Deku_0-1742741631802.png

 


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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).

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
lbendlin
Super User
Super User

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?

 

lbendlin_0-1742735646174.png

 

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.

Not possible in DAX.

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.


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.