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

12

12

12

12

 

but for the running sum to be based on the prior value of its own field/column - intellisense will not allow that measure to get written because of circular reference.  The result sought is:

 

 5    5

 5    10

 5    12

-2   10

-1    9

 5   12

 

perhaps there is another function that will work - or approach

 

would welcome advice on this one - has me stumped...... I should add one can assume that adding an Index column is possible.

 

Anonymous

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

     

14 Replies

  • 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.