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. Below is a screen shot from Excel. The required function column is what I need to get to.
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. (I've blogged about that here: Link to Blogpost
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
This has resolved the problem. Works perfectly and has not slowed down the report. Thanks