How to restart running total column with Power BI Desktop DAX

0 votes

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 by Rajiv
• 8,910 points
1,591 views

1 answer to this question.

0 votes

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 Gitika
• 65,910 points

Related Questions In Power BI

0 votes
1 answer

How to rename a column using DAX in Power BI?

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

answered Aug 22, 2019 in Power BI by anonymous
• 33,030 points

edited Dec 31, 2021 by Soumya 32,907 views
0 votes
1 answer

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

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

answered Sep 27, 2018 in Power BI by Kalgi
• 52,360 points
1,732 views
0 votes
1 answer

How to show filtered vs total data in power BI?

Hi sindhu, Follow below steps to show filtered ...READ MORE

answered Mar 26, 2019 in Power BI by Cherukuri
• 33,030 points
4,226 views
+1 vote
1 answer

How to resolve Power BI DAX Join error?

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

answered May 15, 2019 in Power BI by Avantika
• 1,520 points

edited May 15, 2019 by Omkar 3,473 views
0 votes
1 answer

Understanding the DAX CALCULATE function

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

answered Sep 28, 2020 in Power BI by Gitika
• 65,910 points
1,254 views
0 votes
1 answer

DAX Userelationship function

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

answered Oct 5, 2020 in Power BI by Gitika
• 65,910 points
818 views
0 votes
1 answer

Using RELATED function in DAX with USERELATIONSHIP

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

answered Nov 18, 2020 in Power BI by anonymous
• 65,910 points
4,366 views
0 votes
1 answer

DAX Calculate function with and without FILTER

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

answered Sep 24, 2020 in Power BI by Gitika
• 65,910 points
3,337 views
0 votes
1 answer

Using DAX calculation how to calculate monthly budget till today in power bi Desktop?

You can make use of this: MTD Budget ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,910 points
1,533 views
+2 votes
3 answers

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8, 2019 in Power BI by Cherukuri
• 33,030 points
152,455 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP