Running Sum variant

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

Please take a note:

new_BookableResource is the name that I want to group by

and Leave Taken is such as "Debit/Credit"

Thanks

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

Hi  MarcelBeug,

Already tried it and learn your code per row.

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],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"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))),
    #"Added Index" = Table.AddIndexColumn(TableWithRunningSum, "Index", 1, 1),
    #"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

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".

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],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Type", type text}, {"Debit/Credit", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"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

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,

Works perfect!

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