cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Memorable Member

## Running Sum variant

say there is a Values column

5

5

5

-2

-1

5

the Running Sum measure would be:

5

10

15

13

12

17

if one needed a maximum of 12 and applied a simple IF/Switch result would be

5

10

12

12

12

12

but for the running sum to be based on the prior value of its own field/column - intellisense will not allow that measure to get written because of circular reference.  The result sought is:

5    5

5    10

5    12

-2   10

-1    9

5   12

perhaps there is another function that will work - or approach

would welcome advice on this one - has me stumped...... I should add one can assume that adding an Index column is possible.

@Anonymous

1 ACCEPTED SOLUTION
Community Champion

It should be something like this:

```let
Source = Excel.Workbook(File.Contents("D:\Projects\Internal CRM\Leave Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Type", type text}, {"Debit/Credit", type number}}),
RunningSum = List.Skip(List.Accumulate(#"Changed Type"[#"Debit/Credit"],{0},(sum,value) => sum & {List.Min({12,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns(#"Changed Type")&{RunningSum},Value.Type(Table.AddColumn(#"Changed Type","Running Sum", each 0, type number)))
in
TableWithRunningSum```

Specializing in Power Query Formula Language (M)
14 REPLIES 14
Community Champion

You can try Power Query, it has no intellisense

`let    Source = Table.Buffer(#table(type table[Values = Int64.Type],List.Zip({{5,5,5,-2,-1,5}}))),    RunningSum = List.Skip(List.Accumulate(Source[Values],{0},(sum,value) => sum & {List.Min({12,List.Last(sum)+value})})),    TableWithRunningSum = Table.FromColumns(Table.ToColumns(Source)&{RunningSum},Value.Type(Table.AddColumn(Source,"Running Sum", each 0, Int64.Type)))in    TableWithRunningSum`

Remark: the part in the last step:

`Value.Type(Table.AddColumn(Source,"Running Sum", each 0, Int64.Type))`

takes care of naming and typing the columns of the resulting table, using the table definition from Source with an added column that has the right name and type, but with a dummy value of 0.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi @MarcelBeug,

Thanks for replying, can you explain how to change the name? I'm actually using Dynamics as the data source. And for testing I create dummy data in excel for it.

And I still not familiar with Power Query.

Thanks,
Regards,
Connie

Community Champion

If you mean the name of the column, then adjust the name in double quotes ("Running Sum") in the last step.

You can copy my code from step 2 onwards and add it to your query, similar to this video I just creaed for another question.

In case you use Direct Query mode, I don't think my solution won't work.

Otherwise I didn't quite understand your question, so I hope I provided the answer you are looking for.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi @MarcelBeug,

I already put the explanation there. And I'm interested with your method I hope it will work.

And in there there is my pbix file maybe you can guide me.

Thank you

Community Champion

It should be something like this:

```let
Source = Excel.Workbook(File.Contents("D:\Projects\Internal CRM\Leave Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Type", type text}, {"Debit/Credit", type number}}),
RunningSum = List.Skip(List.Accumulate(#"Changed Type"[#"Debit/Credit"],{0},(sum,value) => sum & {List.Min({12,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns(#"Changed Type")&{RunningSum},Value.Type(Table.AddColumn(#"Changed Type","Running Sum", each 0, type number)))
in
TableWithRunningSum```

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi @MarcelBeug,

Thank you and it works in power bi!! Now I will try to implement it that with Data source Dynamics.

I have a few question:

1. It will be sorted by date for the runningsum? Or I need to add something to make it sorted by date?

2. Can I use group by? example:

So when later I filter by A the running sum will be right value.

Thanks!

Community Champion

That will be quite a different solution.

In the code below, replace "LeaveData" by the name of your source data.

```let

fnRunningSum = (MyTable as table, MyDateColumn as text,  MyLeaveColumn as text, MyRunningSumColumnName as text, MyMaxValue as number) as table =>
let
SortedOnDate = Table.Sort(MyTable, {MyDateColumn, Order.Ascending}),
RunningSum = List.Skip(List.Accumulate(Table.Column(MyTable,MyLeaveColumn),{0},(sum,value) => sum & {List.Min({MyMaxValue,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns(MyTable)&{RunningSum},Value.Type(Table.AddColumn(MyTable,MyRunningSumColumnName, each 0, type number)))
in
TableWithRunningSum,

Source = LeaveData,
#"Grouped Rows" = Table.Group(#"Added Index", {"Name"}, {{"AllData", each fnRunningSum(_,"Date", "Leave Taken", "RunningSum", 20), Value.Type(Table.AddColumn(#"Added Index","RunningSum", each 0, type number))}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Date", "Leave Taken", "OriginalSort", "RunningSum"}),
#"Sorted Rows1" = Table.Sort(#"Expanded AllData",{{"OriginalSort", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"OriginalSort"})
in
#"Removed Columns"```
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi,

Is there any other way? I already found that if we want to sort it I just change #"Changed Type" with #"Sorted Rows"

```    RunningSum = List.Skip(List.Accumulate( #"Sorted Rows""[#"Debit/Credit"],{0},(sum,value) => sum & {List.Min({12,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns( #"Sorted Rows")&{RunningSum},Value.Type(Table.AddColumn( #"Sorted Rows","Running Sum", each 0, type number)))
in
TableWithRunningSum```

and it will sorted by the date that I already sorted before.

And I still don't understand about the grouping there it seems when I apply your code I always get error they can't found "Leave Type" but actually I already create the "Leave Type" column first before apply your code.

Please take a look for my code

```let
Source = OData.Feed("https://xxxxx.crm5.dynamics.com/api/data/v8.2/"),
new_leavesummaries_table = Source{[Name="new_leavesummaries",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(new_leavesummaries_table,{"new_leavetype", "new_entrystatus", "new_description", "new_date", "new_totalleave", "new_BookableResource", "new_Project"}),
#"Expanded new_BookableResource" = Table.ExpandRecordColumn(#"Removed Other Columns", "new_BookableResource", {"name"}, {"new_BookableResource.name"}),
#"Expanded new_Project" = Table.ExpandRecordColumn(#"Expanded new_BookableResource", "new_Project", {"msdyn_subject"}, {"new_Project.msdyn_subject"}),
#"Changed Type with Locale" = Table.TransformColumns(#"Expanded new_Project",{{"new_date", DateTimeZone.ToLocal}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type with Locale", "Leave Taken", each if [new_leavetype] = "Medical Leave" then "0" else if [new_leavetype] = "Childcare Leave" then "0" else if [new_leavetype] = "Other Leave" then "0" else [new_totalleave] ),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Leave Taken", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"new_date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"new_BookableResource.name"}, {{"Leave", each _, type table}}),
#"Expanded Leave" = Table.ExpandTableColumn(#"Grouped Rows", "Leave", {"new_leavetype", "new_entrystatus", "new_description", "new_date", "new_totalleave", "new_BookableResource.name", "new_Project.msdyn_subject", "Leave Taken"}, {"Leave.new_leavetype", "Leave.new_entrystatus", "Leave.new_description", "Leave.new_date", "Leave.new_totalleave", "Leave.new_BookableResource.name", "Leave.new_Project.msdyn_subject", "Leave.Leave Taken"})
in
#"Expanded Leave"```

Here is my code before applying your code, because I need to clean my data first from dynamics

new_BookableResource is the name that I want to group by

and Leave Taken is such as "Debit/Credit"

Thanks

Community Champion

Only iIf you demonstrate that you tried my solution and it doesn't work, I'm willing to take another look.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi  @MarcelBeug,

Here is the final code but the result still wrong

```let
fnRunningSum = (MyTable as table, MyDateColumn as text,  MyLeaveColumn as text, MyRunningSumColumnName as text, MyMaxValue as number) as table =>
let
SortedOnDate = Table.Sort(MyTable, {MyDateColumn, Order.Ascending}),
RunningSum = List.Skip(List.Accumulate(Table.Column(MyTable,MyLeaveColumn),{0},(sum,value) => sum & {List.Min({MyMaxValue,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns(MyTable)&{RunningSum},Value.Type(Table.AddColumn(MyTable,MyRunningSumColumnName, each 0, type number)))
in
TableWithRunningSum,
Source = Excel.Workbook(File.Contents("D:\Projects\Internal CRM\Leave Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Type", type text}, {"Debit/Credit", type number}}),
RunningSum = List.Skip(List.Accumulate(#"Changed Type"[#"Debit/Credit"],{0},(sum,value) => sum & {List.Min({20,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns(#"Changed Type")&{RunningSum},Value.Type(Table.AddColumn(#"Changed Type","Running Sum", each 0, type number))),
#"Grouped Rows" = Table.Group(#"Added Index", {"BookableName"}, {{"AllData", each fnRunningSum(_,"Date", "Debit/Credit", "RunningSum", 20), Value.Type(Table.AddColumn(#"Added Index","RunningSum", each 0, type number))}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"BookableName", "Date", "Type", "Debit/Credit", "Running Sum", "Index", "RunningSum"}, {"AllData.BookableName", "AllData.Date", "AllData.Type", "AllData.Debit/Credit", "AllData.Running Sum", "AllData.Index", "AllData.RunningSum"}),
#"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"AllData.Index", Order.Ascending}})
in
#"Sorted Rows"```

When I tried to put the RunningSum into table it gives me wrong data.

I filter by the BookableName, for A it's true start from 16, but if I filter by B it starts from 20.

I thought it still running sum from A's data before.

Please take a look on my file here.

Thanks,

Regards,

Connie

Community Champion

You still have a "Running Sum" calculation outside the function. When you expand the nested grouped tables, you use that column "Running Sum" (with a space) instead of "RunningSum" that is calculated inside the function.

So basically you should remove from your main query (not from the function!): the steps "RunningSum" and "TableWithRunningSum", and adjust the table expansion to use column "RunningSum" (that is created inside the fnction) instead of "Running Sum".

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi @MarcelBeug,

Tried with Excel as the Data source it works, but when you add new data with earlier date it not works.

Here my code in Excel

```let
fnRunningSum = (MyTable as table, MyDateColumn as text,  MyLeaveColumn as text, MyRunningSumColumnName as text, MyMaxValue as number) as table =>
let
SortedOnDate = Table.Sort(MyTable, {MyDateColumn, Order.Ascending}),
RunningSum = List.Skip(List.Accumulate(Table.Column(MyTable,MyLeaveColumn),{0},(sum,value) => sum & {List.Min({MyMaxValue,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns(MyTable)&{RunningSum},Value.Type(Table.AddColumn(MyTable,MyRunningSumColumnName, each 0, type number)))
in
TableWithRunningSum,
Source = Excel.Workbook(File.Contents("D:\Projects\Internal CRM\Leave Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Type", type text}, {"Debit/Credit", type number}}),
#"Grouped Rows" = Table.Group(#"Added Index", {"BookableName"}, {{"AllData", each fnRunningSum(_,"Date", "Debit/Credit", "RunningSum", 20), Value.Type(Table.AddColumn(#"Added Index","RunningSum", each 0, type number))}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"BookableName", "Date", "Type", "Debit/Credit", "Index", "RunningSum"}, {"AllData.BookableName", "AllData.Date", "AllData.Type", "AllData.Debit/Credit", "AllData.Index", "AllData.RunningSum"}),
#"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"AllData.Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"AllData.Index"})
in
#"Removed Columns"```

Here I attach the picture:

I add the data in Excel

And here the result in Power BI.

So for the calculations seems not sorted by date yet.

Thanks,

Regards,

Connie

Community Champion

You're right. A few occurrences of "MyTable" in the function must be replaced by "SortedOnDate":

```    fnRunningSum = (MyTable as table, MyDateColumn as text,  MyLeaveColumn as text, MyRunningSumColumnName as text, MyMaxValue as number) as table =>
let
SortedOnDate = Table.Sort(MyTable, {MyDateColumn, Order.Ascending}),
RunningSum = List.Skip(List.Accumulate(Table.Column(SortedOnDate,MyLeaveColumn),{0},(sum,value) => sum & {List.Min({MyMaxValue,List.Last(sum)+value})})),
TableWithRunningSum = Table.FromColumns(Table.ToColumns(SortedOnDate)&{RunningSum},Value.Type(Table.AddColumn(SortedOnDate,MyRunningSumColumnName, each 0, type number)))
in
TableWithRunningSum,```
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Works perfect!

Thank you very much! and sorry for troubleing too much for this one!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors