DAX - sum values from previous row and reset if 0

[MarcinJan]

Hi Everyone, 

I am using a DAX formula to create column that sums the value from previous column.

Sum = CALCULATE(SUM('Log_Check'[Peak]),FILTER('Log_Check','Log_Check'[Date]<=EARLIER('Log_Check'[Date])))

but if value is 0, I want the counter to reset and start counting from the 0.

Any ideas ?

I tried adding IF with checking if the value=0, but then I do not know how to substract the last value, so that sum resets to 0 too. Thanks!

Date	Value	Sum
1/02/2021	1	1
2/02/2021	1	2
3/02/2021	-1	1

Value	Sum
1	1
1	2
-1	1
1	2
0	0
1	1

[MarcinJan]

works perfectly. Thank you! Now I know to use the term "cumulative sum/total"

[tackytechtom · Accepted solution]

Hi @MarcinJan ,

Column 2 might be what you are after:

Here the DAX:

Column 2 = 
VAR _currentValue = 'Table'[Value]

VAR _currentDate = 'Table'[Date]

VAR _sum = 
    SUMX ( 
        FILTER ('Table', 'Table'[Date] <= _currentDate), 
        'Table9'[Value]
    )
VAR _last0 = 
CALCULATE ( 
    MAX ('Table'[Date] ), 
    FILTER('Table', 'Table'[Date] < _currentDate && 'Table9'[Value] = 0)
)

RETURN 
IF ( 
    _currentValue = 0, 
    0,
    SUMX ( 
        FILTER ( 'Table', 'Table'[Date] >= _last0 && 'Table'[Date] <= _currentDate ), 
        'Table'[Value] 
    )
)

I took the code from here and adapted it a bit:

Cumulative Sum column. Resets if row value = 0 - Microsoft Power BI Community

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/