Calculated total balance column can't be negative

[DarkEnergy22]

Hello Community,

I've tried to work this out by myself with IF statements and variables but I can't think of anything anymore. I have a balance column measure that needs to be populated by the sum total of the JBalance[Mutations] chronologically.

The table is sorted on Date and has an index but I can't for the life of me construct it in a way where the output can't be lower than 0 and the measure starts calculating from 0 again. This is what I got right now:

Balance = CALCULATE(SUM('JBalance'[Mutation]),FILTER('JBalance','JBalance'[Index]<=EARLIER('JBalance'[Index])))

I need it to dynamically adjust the FILTER range when it reaches a result of lower than 0. I can make it output a 0 by an IF statement but can't think of a way to update the FILTER to start over from 0 (as in adjust the filter on JBalance'[Index], disregarding the previous rows so the SUM total starts calculating from current row)

Solution output:

[rfigtree]

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-total-that-starts-over-when-the-to...

[daxer-almighty]

Hi @rfigtree 

I've adjusted the post you gave us a link to to calculate what @DarkEnergy22 wants. Indeed, this works. I was convinced a formula could not be crafted for this but apparently I was wrong 🙂 Each day something new, it seems.

// So, the first calculated column must be:
[Cumulative Sum] =
var vCurrentDate = T[Date]
return
sumx(
    filter( T, T[Date] <= vCurrentDate),
    T[Mutation]
)

// The second column will be:
[Cummin of Cumulative Sum] =
var vMin =
minx(
    filter( T, T[Date] <= vCurrentDate),
    T[Cumulative Sum]
)
return
min( 0, vMin )

// The final column:
[Cumulative Sum Adjusted] =
    T[Cumulative Sum] - T[Cummin of Cumulative Sum]

[daxer-almighty]

@DarkEnergy22 

Mate, save yourself grief and calculate this in Power Query. Your formula is recursive by nature and you can forget about calculating this is DAX. Such recursive formulas are not possible in this language. Power Query or the language of the data source is your only chance.

[Anonymous]

Hello @DarkEnergy22 ,
It's happening because of the balance of the index is less and mutation of the index 39 is more.
Can you provide the data in table format instead of image?