How to restart running total column with Power BI Desktop DAX

Question

I have a table where every person has a record for every day of the year. I used this 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 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

Answer 1

This is not only a running total with a condition, but also a nested/clustered one, as the logic has to applied on the ID-level. For large tables, M is better at it than DAX, as it doesn't use as much RAM. 

The following function adapts that logic to the current case and has to be applied on ID-level: (Required column names are: "Type", "Daily Allowance", "Adjustments")

(MyTable as table) => let SelectJustWhatsNeeded = Table.SelectColumns(MyTable,{"Type", "Daily Allowance", "Adjustments"}), ReplaceNulls = Table.ReplaceValue(SelectJustWhatsNeeded,null,0,Replacer.ReplaceValue,{"Adjustments"}), #"Merged Columns" = Table.CombineColumns(ReplaceNulls,{"Daily Allowance", "Adjustments"}, List.Sum,"Amount"), TransformToList = List.Buffer(Table.ToRecords(#"Merged Columns")), ConditionalRunningTotal = List.Skip(List.Generate( () => [Type = TransformToList{0}[Type], Result = 0, Counter = 0], each [Counter] <= List.Count(TransformToList), each [ Result = if TransformToList{[Counter]}[Type] = "working" and [Result] < 0 and [Type] <> "working" then TransformToList{[Counter]}[Amount] else TransformToList{[Counter]}[Amount] + [Result] , Type = TransformToList{[Counter]}[Type], Counter = [Counter] + 1 ], each [Result] )), Custom1 = Table.FromColumns( Table.ToColumns(MyTable) & {ConditionalRunningTotal}, Table.ColumnNames(MyTable) & {"Result"} ) in Custom1