cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 🙂

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

3 REPLIES 3
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 🙂

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

New Member

hiya, thanks for solution! Is it possible to add one more condition that groups by another column?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors