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

    • 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