cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
14 REPLIES 14
Highlighted

Hi  @MarcelBeug,

 

Already tried it and learn your code per row.

Here is the final code but the result still wrong

let
    fnRunningSum = (MyTable as table, MyDateColumn as text,  MyLeaveColumn as text, MyRunningSumColumnName as text, MyMaxValue as number) as table =>
    let
        SortedOnDate = Table.Sort(MyTable, {MyDateColumn, Order.Ascending}),
        RunningSum = List.Skip(List.Accumulate(Table.Column(MyTable,MyLeaveColumn),{0},(sum,value) => sum & {List.Min({MyMaxValue,List.Last(sum)+value})})),
        TableWithRunningSum = Table.FromColumns(Table.ToColumns(MyTable)&{RunningSum},Value.Type(Table.AddColumn(MyTable,MyRunningSumColumnName, each 0, type number)))
    in
        TableWithRunningSum,
    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({20,List.Last(sum)+value})})),
    TableWithRunningSum = Table.FromColumns(Table.ToColumns(#"Changed Type")&{RunningSum},Value.Type(Table.AddColumn(#"Changed Type","Running Sum", each 0, type number))),
    #"Added Index" = Table.AddIndexColumn(TableWithRunningSum, "Index", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"BookableName"}, {{"AllData", each fnRunningSum(_,"Date", "Debit/Credit", "RunningSum", 20), Value.Type(Table.AddColumn(#"Added Index","RunningSum", each 0, type number))}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"BookableName", "Date", "Type", "Debit/Credit", "Running Sum", "Index", "RunningSum"}, {"AllData.BookableName", "AllData.Date", "AllData.Type", "AllData.Debit/Credit", "AllData.Running Sum", "AllData.Index", "AllData.RunningSum"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"AllData.Index", Order.Ascending}})
in
    #"Sorted Rows"

When I tried to put the RunningSum into table it gives me wrong data. 

I filter by the BookableName, for A it's true start from 16, but if I filter by B it starts from 20.

I thought it still running sum from A's data before.

 

aaaa.png

 

Please take a look on my file here.

 

Thanks,

Regards,

Connie

 

 

 

 

 

Highlighted

You still have a "Running Sum" calculation outside the function. When you expand the nested grouped tables, you use that column "Running Sum" (with a space) instead of "RunningSum" that is calculated inside the function.

 

So basically you should remove from your main query (not from the function!): the steps "RunningSum" and "TableWithRunningSum", and adjust the table expansion to use column "RunningSum" (that is created inside the fnction) instead of "Running Sum".

Specializing in Power Query Formula Language (M)
Highlighted

Hi @MarcelBeug,

 

Tried with Excel as the Data source it works, but when you add new data with earlier date it not works. 

Here my code in Excel 

 

let
    fnRunningSum = (MyTable as table, MyDateColumn as text,  MyLeaveColumn as text, MyRunningSumColumnName as text, MyMaxValue as number) as table =>
    let
        SortedOnDate = Table.Sort(MyTable, {MyDateColumn, Order.Ascending}),
        RunningSum = List.Skip(List.Accumulate(Table.Column(MyTable,MyLeaveColumn),{0},(sum,value) => sum & {List.Min({MyMaxValue,List.Last(sum)+value})})),
        TableWithRunningSum = Table.FromColumns(Table.ToColumns(MyTable)&{RunningSum},Value.Type(Table.AddColumn(MyTable,MyRunningSumColumnName, each 0, type number)))
    in
        TableWithRunningSum,
    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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"BookableName"}, {{"AllData", each fnRunningSum(_,"Date", "Debit/Credit", "RunningSum", 20), Value.Type(Table.AddColumn(#"Added Index","RunningSum", each 0, type number))}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"BookableName", "Date", "Type", "Debit/Credit", "Index", "RunningSum"}, {"AllData.BookableName", "AllData.Date", "AllData.Type", "AllData.Debit/Credit", "AllData.Index", "AllData.RunningSum"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"AllData.Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"AllData.Index"})
in
    #"Removed Columns"

Here I attach the picture:

I add the data in Excel

v.png

 

And here the result in Power BI. 

aaaaaaa.png

 

So for the calculations seems not sorted by date yet.

 

Thanks,

Regards,

Connie

 

Highlighted

You're right. A few occurrences of "MyTable" in the function must be replaced by "SortedOnDate":

 

fnRunningSum.png

 

    fnRunningSum = (MyTable as table, MyDateColumn as text,  MyLeaveColumn as text, MyRunningSumColumnName as text, MyMaxValue as number) as table =>
    let
        SortedOnDate = Table.Sort(MyTable, {MyDateColumn, Order.Ascending}),
        RunningSum = List.Skip(List.Accumulate(Table.Column(SortedOnDate,MyLeaveColumn),{0},(sum,value) => sum & {List.Min({MyMaxValue,List.Last(sum)+value})})),
        TableWithRunningSum = Table.FromColumns(Table.ToColumns(SortedOnDate)&{RunningSum},Value.Type(Table.AddColumn(SortedOnDate,MyRunningSumColumnName, each 0, type number)))
    in
        TableWithRunningSum,
Specializing in Power Query Formula Language (M)
Highlighted

Works perfect!

 

Thank you very much! and sorry for troubleing too much for this one!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors