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

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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