Power BI Desktop DAX restart running total column

Overview

This is a challenging thing to ask PowerBI to do, so a tidy approach may be difficult to find.

The biggest issue is that PowerBI’s data model does not support the concept of a running tally – at least not the way we do it in Excel. In Excel, a column can reference values that occur in the ‘previous row’ of that same column and then be adjusted by some ‘daily change’ listed in a different column.

PowerBI can only imitate this by adding up all the daily changes over some subset of rows. We take the date value in our current row and create a filtered table where all dates are less than this current row’s date, and then sum up all the daily changes from that subset. This may seem to be a subtle difference, but it is quite significant:

This means that there’s no way to ‘override’ our running total. The only math that’s being done is happening on the column containing daily changes – the column containing ‘running total’ is only a result – it is never used in any subsequent row’s calculation.

We must abandon the concept of ‘reset’ and instead imagine making a column that contains an ‘adjustment’ value. Our adjustment will be a value that can be included so that when the described conditions are met, the total of daily balances and adjustments will sum to 1.

If we look at the calculated running given by OP, we see that the value of our running total on a ‘non-working’ day just prior a ‘working’ day gives us that needed amount that, if reversed, would sum to zero and cause the running total on each following working day to increase by one. This is our desired behavior (with one problem to be described later on).

Result

Most Recent Date Prior to Work = 

CALCULATE(
Max(Leave[Date]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] = EARLIER(Leave[Date]) -1 && Leave[Type] <> "Working" && Earlier(Leave[Type]) = "Working"
))

It helps to know the difference between row and filter contexts and how EARLIER operates to follow this calculation. In this scenario, you can think of "EARLIER" as meaning 'this reference points to the value in the current row" and otherwise a reference points to the whole table returned by "ALLEXCEPT(Leave, Leave[Id])." In this way, we find the places where the current row has type "Working" and the prior day's row has some other type.

Most Recent Date Prior to Work Complete = 

CALCULATE(
Max(Leave[Most Recent Date Prior to Work]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] <= EARLIER(Leave[Date])
))

This calculation imitates a 'fill down' kind of operation. It says, "When looking at all the rows whose date is before the date on THIS row, return the biggest value in 'Most Recent Date Prior to Work."

Daily Balance Adjustment = 

CALCULATE(
SUM(Leave[Running Daily Balance]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] = EARLIER(Leave[Most Recent Date Prior to Work Complete])
))

Now that every row has a field explaining where to go to find the daily balance to use as our adjustment, we can just go look it up from the table.

Adjusted Daily Balance = Leave[Running Daily Balance] - Leave[Daily Balance Adjustment]

And finally we apply the adjustment to our running total for the final result.

The Issue

This approach fails to address that the count should not reset unless the running daily balance is below zero. I have been proved wrong before, but I would say that this can't be accomplished in DAX alone because it creates a circular dependency. Essentially, you make a requirement: use the aggregated value to determine what should be included in the aggregation.

So that's as far I can bring you. Hope it helps.

I think I have it!

Here's the result, building upon the solution I posted earlier: (The data has been modified to show off more "work / no work" behaviors and use cases)

RESULT

DETAILS

(1) Drop "Adjusted Running Daily Balance" and "Daily Balance Adjustment" Colums. We'll get the same outcome with one less step in just a moment.

(2) Create the following column (RDB = "running daily balance")...

Grouped RDB = 

CALCULATE(
SUM(Leave[Daily Balance]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id], Leave[Most Recent Date Prior to Work Complete]),
   Leave[Date] <= EARLIER(Leave[Date]) 
))

Having created the "Most Recent Date Prior to Work Complete," we have actually the piece needed to do our 'reset' that I claimed was impossible before. By filtering on this field, we have the opportunity to start each slice at '1'

(3) We still have the same problem tho, we can't look at the result in our column and use it to decide what to do later in that same column. But we CAN build a new adjustment column that will hold that info! And we already have a reference to 'Most Recent Date Prior to Work' -- that's the last day in the previous group... the row with the information we need!

Grouped RDB Adjustment = 

VAR CalculatedAdjustment =
CALCULATE(
SUM(Leave[Grouped RDB]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] IN SELECTCOLUMNS(
        FILTER(
            Leave,
            Leave[Most Recent Date Prior to Work] <> BLANK() &&
            Leave[id] = EARLIER(Leave[Id])), "MRDPtW", Leave[Most Recent Date Prior to Work]) &&
   Leave[Most Recent Date Prior to Work Complete] < EARLIER(Leave[Most Recent Date Prior to Work Complete]) &&
   Leave[Most Recent Date Prior to Work Complete] <> Blank()
))

RETURN if (CalculatedAdjustment > 0, CalculatedAdjustment, 0)

So we look at the last day in Each prior group and if the total sum of those adjustments has a positive value we apply it and if it's negative we leave it alone instead. Also, if our person's first few days are non-working days, we don't want that initial negative bit in our adjustment at all so it get's filtered away too.

(4) This last step will bring the adjustment into the final result. Sum up the two new columns and we should finally have our Adjusted Running Daily Balance. Voila!

Adjusted Running Daily Balance = Leave[Grouped RDB] + Leave[Grouped RDB Adjustment]

We built a lot of extra columns along the way to this result which usually isn't my favorite thing to do. But, this was a tricky one.