Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MarcinJan
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!
 
DateValueSum
1/02/202111
2/02/202112
3/02/2021-11

 

 

 

 

ValueSum
11
12
-11
12
00
11

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @MarcinJan ,

 

Column 2 might be what you are after:

tomfox_0-1649704471020.png

 

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! linkedIn

#proudtobeasuperuser 

View solution in original post

3 REPLIES 3
MarcinJan
Frequent Visitor

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

tackytechtom
Super User
Super User

Hi @MarcinJan ,

 

Column 2 might be what you are after:

tomfox_0-1649704471020.png

 

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! linkedIn

#proudtobeasuperuser 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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