Warehouse Utilised Capacity by Day

Hi everyone, been using PowerBI for quite a while now and finding it incredibly useful.

I've been exploring getting information for the storage of materials in the warehouse for any period of time.

Right now, we have a table that looks something like this. So the products are planned by the date required. I know how to unpivot the date columns to get a date table connected to show when the product will be out of the warehouse / or required by the end user.

Item	Total Qty	Holding Area	Holding Duration	1/1/2019	7/1/2019	13/1/2019	19/1/2019	25/1/2019	31/1/2019	6/2/2019	12/2/2019	18/2/2019	24/2/2019
Product A	500	Zone 1A	90					300		200
Product B	2000	Zone 1B	90				500		500		500		500
Product C	250	Zone 1A	180			150		100
Product C	250	Zone 1B	30				100		150

Now the challenge is for me to get the information on the current holding in each zone by each day. Since the dates will convert into the "Holding End Date", I can easily minus the "Holding Duration" to get the "Holding Start Date". However, what I'll require is to show the total utilised capacity of the warehouse at any point in time.

From the data above, I should be able to see that from on 31 Jan 2019, the total utilised capacity should be 1850 across the various zones (assuming that the product is still held in the warehouse on the required by date). Is there some way to setup a new table with these information, which would meet my requirements? Ideally, my guess is that there will be a row for every day for every single item, replicating the qty as long as the date is between the start and end date.

If there's a better alternative to get the information required, that will be great as well!

Need Help

Ashish_Mathur

7 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Total Qty", Int64.Type}, {"Holding Area", type text}, {"Holding Duration", Int64.Type}, {"01-01-2019", type any}, {"07-01-2019", type any}, {"13-01-2019", Int64.Type}, {"19-01-2019", Int64.Type}, {"25-01-2019", Int64.Type}, {"31-01-2019", Int64.Type}, {"06-02-2019", Int64.Type}, {"12-02-2019", Int64.Type}, {"18-02-2019", type any}, {"24-02-2019", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item", "Total Qty", "Holding Area", "Holding Duration"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Holding end date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Holding end date", type date}}, "en-IN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Holding start date", each Date.AddDays([Holding end date],-[Holding Duration])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {Number.From([Holding start date])..Number.From([Holding end date])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Holding end date", "Holding start date"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}}),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Renamed Columns1", {{"Date", type date}}, "en-IN"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale1",{{"Value", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Item", "Total Qty", "Holding Area", "Holding Duration", "Date", "Value"})
in
    #"Reordered Columns"

Hope this helps.

Forum Discussion

Warehouse Utilised Capacity by Day

7 Replies

Related content

utilisation vs capacity

Scaling up to down fabric capacity giving utilisation error.

Warehouse Scaling

Warehouse : Long-Running Query Detected – Impacting Fabric Capacity

warehouse vs lakehouse