Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Subtracting Orders from Grouped Stock whilst adding in Stock Delivery's "Help Please!!"

Hi I am really struggling here, I am trying to create a new Column called "Running Stock" I can easily create this in Excel but Power Query is causing me a headache!

The calculation is needed by group and date order, The first calc subtracts the "Forecast qty" from the "Stock Total" the following calculation needs to subtract the "Forecast qty" from the first value of the "Running Stock" + any value in the "On order BF required date"

This then needs to reset at the next group.

I have tried to refer to the previous row using grouped index columns but cant work out how to start this calc after the first calc from a different column? and keeping it in date order?

Below is how I want the report to look:



I have tried to show with colour coding what cells i need to subrtact and add together.

Any assitance with this would be greatly apprecaited

Solution Supplier
Solution Supplier

Hi @Stubrg ,


You can try this:

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdG9CsIwEAfwd8lcuK80TVYfwMWxdBI3QRDF1/cu1jZpCoUcyfDj+r/rODokQA+MLK5zl+vz9tGbtAYtzPfUKeMdJi3zgP2WDQ2jBBiOGQsQNtksXNLitR2DHnW+dNI4mzW7/sBJMcXpcX/pFbT8nM4vs4aNsh45Ba+OfJFudtG299/yj3FqmXWWWLHqf53fpsi+JTiH00d2EfRUrt9hul+imtkrLC3NTV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Due Date" = _t, Part = _t, #"Forecast qty" = _t, #"Stock Total" = _t, #"On order BF required date" = _t, #"Quantity in Store" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Forecast qty", Int64.Type}, {"Stock Total", Int64.Type}, {"On order BF required date", Int64.Type}, {"Quantity in Store", Int64.Type}}),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", 
            {"AllRows", each 
                    ListForecast = List.Buffer([Forecast qty]),
                    ListSOrderBF = List.Buffer([On order BF required date]),
                    RunningStock = 
                            ()=> [RT = [Stock Total]{0} - ListForecast{0} , Counter = 0 ], /* record first row */
                            each [Counter] < List.Count(ListForecast),
                            each [
                                RT = List.Sum({[RT], -ListForecast{[Counter]+1}, ListSOrderBF{[Counter]+1}}),
                                Counter = [Counter] +1
                            ],  /* record next row */
                            each [RT] /*select RT as result*/
                in /* transform table to columns, add new column and back to table */
                        Table.ToColumns(_) & { Value.ReplaceType(RunningStock, type {Int64.Type})},
                        Table.ColumnNames(_) & {"Running Stock"}
    Expanded = Table.Combine(#"Grouped Rows"[AllRows])




You can find explantion here Compute a Running Total by Category in Power Query - BI Gorilla

and here Quickly Create Running Totals in Power Query • My Online Training Hub

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors