Forum Discussion

LCarr's avatar
LCarr
Frequent Visitor
6 years ago

Power BI Running Total Restart Function

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
Need Help

3 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      LCarr
      Frequent Visitor
      6 years ago

      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's avatar
        dax
        Community Support
        6 years ago

        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.