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

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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!

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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"