Forum Discussion
Running Sum variant
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], #"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({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
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.
Hi MarcelBeug,
Can you open my thread? Here is the Link.
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
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], #"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({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 TableWithRunningSumHi 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!
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, #"Added Index" = Table.AddIndexColumn(Source, "OriginalSort", 1, 1), #"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"