Power BI Running Total Restart Function

[LCarr]

Hi,

I have a table where every person has a record for every day of the year.  I used the following function to achieve a running total based on the daily balance column

CALCULATE(

SUM(Leave[Daily Balance]),

FILTER(

ALLEXCEPT(Leave, Leave[Employee Id]),

Leave[Date] <= EARLIER(Leave[Date])

))

but I need the running total to restart from 1 the first time the daily balance is a positive number after the previous row is a negative number.   Below is a screen shot from Excel.  The required function column is what I need to get to.

Thanks

Lynsey

[dax]

Hi @LCarr , 

You could try to use below expressions:

If you want to use column, you could try below

Column = CALCULATE(
SUM(Leave[Balance]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),Leave[type]=EARLIER(Leave[type])&&
Leave[Date] <= EARLIER(Leave[Date])
))

If you want to use measure , you could try below

Measure = CALCULATE(
SUM(Leave[Balance]),
FILTER(
ALLEXCEPT(Leave, Leave[Id],Leave[type]),
Leave[Date] <= MIN(Leave[Date])
))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LCarr]

Thanks, the column is close but I didn't manage to fully explain the issue (sorry first time poster).  I can post a different question if that is more appropriate but I am still looking to get the running total to restart, just with different criteria.

The criteria is 

If Type = Working and the running total of Daily Balance is less than zero AND the Type of the previous row is not equal to Working then restart the running total from 1.

Many Thanks

Lynsey

[dax]

Hi @LCarr 

I am sorry for late reply , you could try to add an index column in table by M code, then use measure to achieve this goal. You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.