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 can try Power Query, it has no intellisense :smileylol:

 

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.

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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