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 1,768 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,890 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

## How to show filtered vs total data in power BI?

Hi sindhu, Follow below steps to show filtered ...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

## Using RELATED function in DAX with USERELATIONSHIP

Here's an example using the AdventureWorksDW data ...READ MORE

## DAX Calculate function with and without FILTER

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