Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredJoin us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.
Hi Everyone,
I am using a DAX formula to create column that sums the value from previous column.
but if value is 0, I want the counter to reset and start counting from the 0.
Any ideas ?
| 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 |
Solved! Go to 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/
| Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
| Also happily accepting Kudos 🙂 |
| Feel free to connect with me on LinkedIn! | |
| #proudtobeasuperuser | |
works perfectly. Thank you! Now I know to use the term "cumulative sum/total"
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/
| Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
| Also happily accepting Kudos 🙂 |
| Feel free to connect with me on LinkedIn! | |
| #proudtobeasuperuser | |
hiya, thanks for solution! Is it possible to add one more condition that groups by another column?
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Check out the October 2025 Power BI update to learn about new features.
| User | Count |
|---|---|
| 78 | |
| 46 | |
| 37 | |
| 31 | |
| 26 |