Forum Discussion
How to enter a initial value to calculate inventory in Power Query or DAX
I have a table with 5 columns in order to calculate inventory per day. I am able to transfer the number from Final Inventory (Inventory - Used + Arrived ) to the next raw in Inventory; however, I dont know how to insert the initial inventory number .in the inventory column.
In excel this is pretty easy; the initial inventory goes in the first cell in the inventory column and the formula starts in the second row.
I would appreciate the help to do this on Power BI using Power Query / DAX or M...
Date // Inventory // Product Qty used // Product arrived // Final Inventory
Hi Anonymous
You can create a blank query and put the follwing code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7bCcAwCAXQXfwOxCe1swT3X6MmloJQUIR74OJawMgyaRIMcETMUxvjM80gx/wwMTa1UuGjal2vUpJS73qXsv0201tNxbrfi3gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Inventory = _t, #" Product Qty used" = _t, #"Product arrived " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Inventory", Int64.Type}, {" Product Qty used", Int64.Type}, {"Product arrived ", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(Table.SelectRows(#"Added Index",(x)=>x[Index]<=[Index])[#" Product Qty used"])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Index",(x)=>x[Index]<=[Index])[#"Product arrived "])), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Final", each if [Index]=1 then [Inventory] else List.Min(Table.SelectRows(#"Added Custom1",(x)=>x[Index]=1)[Inventory])-[Custom]+[Custom.1]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Custom", "Custom.1"}) 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.
2 Replies
Hi Anonymous
You can create a blank query and put the follwing code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7bCcAwCAXQXfwOxCe1swT3X6MmloJQUIR74OJawMgyaRIMcETMUxvjM80gx/wwMTa1UuGjal2vUpJS73qXsv0201tNxbrfi3gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Inventory = _t, #" Product Qty used" = _t, #"Product arrived " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Inventory", Int64.Type}, {" Product Qty used", Int64.Type}, {"Product arrived ", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(Table.SelectRows(#"Added Index",(x)=>x[Index]<=[Index])[#" Product Qty used"])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Index",(x)=>x[Index]<=[Index])[#"Product arrived "])), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Final", each if [Index]=1 then [Inventory] else List.Min(Table.SelectRows(#"Added Custom1",(x)=>x[Index]=1)[Inventory])-[Custom]+[Custom.1]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Custom", "Custom.1"}) 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.
rubayatyasminCommunity Champion
Hi, Anonymous
one way would be using PQ to create a custom column to enter your initial inventory logic. then create another customer col to calculate final inv. like
if [Date] = List.Min(Table[Date]) then [Initial Inventory] else [Inventory] - [Product Qty used] + [Product Arrived]