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.
