cancel
Showing results for
Did you mean: Frequent Visitor

## DAX - sum values from previous row and reset if 0

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

1 ACCEPTED SOLUTION  Super User

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 🙂

Proud to be a Super User!

2 REPLIES 2 Frequent Visitor

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

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 🙂

Proud to be a Super User! Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (3,461)