Running Subtract by Group

[kivancc94]

Hi, 
I would be happy if you could help me.

I need to create a formula for Cumulative subtraction in which "Stock" should be decreased by day and by HE.Code

For example below,

The stock is 17.250. It should be decreased by "Starting Date" and  "HE.Code". Maybe i can use ALLEXCEPT(Table,HE.Code) but i could not create the formula. 

Regards

[v-xinruzhu-msft · Accepted solution]

Hi @kivancc94 

You can refer to the following example.

Create two blank query,then put the following two queries to the Advanced Editor in power query Editor.

1.Create a custom function.(Query1)

(Sum) =>
let
    #"Added Index" = Table.AddIndexColumn(Sum, "Index", 1, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 1 then [Stock] else 0-[Stock]),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each List.Sum(List.FirstN(#"Added Conditional Column"[Custom],[Index]))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "Sum"}})
in
    #"Renamed Columns"

2.Put the function to the table (Query 2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI31jcyV9JRMjQ3MjUA0kbGZpZKsTpUkDQ2BEmamBqB5UwM8cvFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Starting Date" = _t, Stock = _t, HE.Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Starting Date", type date}, {"Stock", Int64.Type}, {"HE.Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HE.Code"}, {{"Count", each _, type table [Starting Date=nullable date, Stock=nullable number, HE.Code=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Query1", each Query1([Count])),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query1", {"Starting Date", "Stock", "Sum"}, {"Starting Date", "Stock", "Sum"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Count"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kivancc94]

Thanks a lot !