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,

 

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

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      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!