Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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))
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
)
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
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |