Forum Discussion

CahabaData's avatar
CahabaData
Memorable Member
8 years ago
Solved

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 ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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

     

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

 

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    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,
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Works perfect!

     

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