cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Memorable Member
Memorable Member

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.

 

@conniedevina

www.CahabaData.com
1 ACCEPTED SOLUTION

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

14 REPLIES 14

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors