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 Dec 8, 2020 in Power BI 658 views

## 1 answer to this question.

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 answered Dec 8, 2020 by
• 65,850 points

## How to rename a column using DAX in Power BI?

Hi Ramya, I found these two function that ...READ MORE

## How to publish .pbix report from power bi desktop to power bi service directly?

To save powerbi file directly to report ...READ MORE

+1 vote

## How to resolve Power BI DAX Join error?

Sample Table Used Here: Table1: Partner_ID, User_Id Table2: User_Id, ...READ MORE

## Understanding the DAX CALCULATE function

Using the CALCULATE function makes the DAX perform a context ...READ MORE

## DAX Userelationship function

You need a way for the measure ...READ MORE

## DAX Calculate function with and without FILTER

You could make [Measure12] return the same results as ...READ MORE