Month by month calculation of quantity in stock

[mtrevisiol]

Hi everyone. 

I would like to create a table that shows me, for each item, and for each month of the year, the change in quantity in stock and the resulting stock, starting with the quantity in stock on January 1st 2021.

This is the table I've got:

Item	Year	Month	RunningQty	Qty
Item A	2021	1	10	-2
Item A	2021	2		5
Item A	2021	3		7
Item B	2021	1	15	0
Item B	2021	3		-10
Item C	2021	1	0	0
Item C	2021	2		5
Item C	2021	3		-2

• "Qty" represents the entered quantity entering or exting the warehouse for each item, for each month
• "Running qty" represents the resulting stock, after entry and exit, at the end of each month

And this is the ideal result:

Item	Year	Month	RunningQty	Qty
Item A	2021	1	10	-2
Item A	2021	2	8	5
Item A	2021	3	13	7
Item B	2021	1	15	0
Item B	2021	3	15	-10
Item C	2021	1	0	0
Item C	2021	2	0	5
Item C	2021	3	5	-2

What formula can I use to calculate the "RunningQty" column?

Here is the pbix file: https://www.dropbox.com/s/wu51fajcuqn3chh/ItemsByMonth.pbix?dl=0

Thank you in advance!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mtrevisiol]

This is exactly the solution I was searching, thank you very much!

[Ashish_Mathur]

You are welcome.

[lbendlin]

"Running qty" represents the resulting stock, after entry and exit, at the end of each month

That is _very_ confusing.  Can you please explain that again?  Not following your logic here. Did you mix up the column names?

Here is a very crude version of a Power Query script that is based on your current sample data in the PBIX.  It needs some modifications for more complete sample data.

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVVwVNJRMjIwMgRSYGwAJHSNlGJ1MOWNgBiITLFKGkMkzRGSTmgmmwIJA6zSUL26hkjSzqi6DVA1O+NzlTOmyUAPxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Year = _t, Month = _t, Qty = _t, #"Running Qty" = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Item", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Qty", Int64.Type}, {"Running Qty", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Modificato tipo", "Index", 0, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Qty],each if [Qty]=null then #"Added Index"[Qty]{[Index]-1}+#"Added Index"[Running Qty]{[Index]-1} else [Qty],Replacer.ReplaceValue,{"Qty"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Qty],each if [Qty]=null then #"Replaced Value"[Qty]{[Index]-1}+#"Replaced Value"[Running Qty]{[Index]-1} else [Qty],Replacer.ReplaceValue,{"Qty"})
in
    #"Replaced Value1"

[lbendlin]

Here is a more complete version

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVVwVNJRMjIwMgRSYGwAJHSNlGJ1MOWNgBiITLFKGkMkzRGSTmgmmwIJA6zSUL26hkjSzqi6DVA1O+NzlTOmyUAPxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Year = _t, Month = _t, Qty = _t, #"Running Qty" = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Item", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Qty", Int64.Type}, {"Running Qty", Int64.Type}}),
    cm = (tb)=>
      let
          #"Added Index" = Table.AddIndexColumn(tb, "Index", 0, 1, Int64.Type),
          #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Qty],each if [Index]=0 then [Qty] else List.Accumulate({0..[Index]-1},#"Added Index"[Qty]{0},(state,current)=>state+#"Added Index"[Running Qty]{current}),Replacer.ReplaceValue,{"Qty"})
       in
      #"Replaced Value",
    #"Grouped Rows" = Table.Group(#"Modificato tipo", {"Item"}, {{"Count", each _, type table [Item=nullable text, Year=nullable number, Month=nullable number, Qty=nullable number, Running Qty=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each cm([Count])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Item", "Year", "Month", "Qty", "Running Qty", "Index"}, {"Item", "Year", "Month", "Qty", "Running Qty", "Index"})
in
    #"Expanded Custom"