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,860 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,890 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 36,673 views
0 votes
0 answers
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,350 points
1,922 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,674 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,890 points
1,553 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,890 points
1,122 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,890 points
4,885 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,890 points
3,946 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,890 points
1,812 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
159,379 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