Inventory Management [Shortage report]

[djpejsen]

Hi Guys

Is it possIble to make an inventory shortage report based on a data model with 3 tables

1. BOM items transactions. [ item Id, transaction date, Warehouse code, required quantity ]
2. Inventory Onhand snap shot [ Item Id, Onhand quantity per warehouse, Warehouse code]
3. Date table [Date, ISO week number, Month, Year, etc.]

Allocate onhand inventory for each BOM Item by FIFO principle

I would like to have an overview, when a BOM item have a shortage, based on transactions date and Onhand inventory for each item Id, in the BOM items transactions.
I would like to aggregate the inventory shortage quantity, per Date or per ISO Week, or per Month.

Regards Jan

[sam.mckay]

Hi Jan,

Anything is possible in Power BI from an analytical perspective.

Probably the best way to go about this is ask specific question on it as you start building.

Also here’s a couple of inventory based tutorials.

I plan in the future to run a member only event on inventory management. Don’t have a date yet on this though.

Inventory Management Solution using Power BI

You can download this model here and I walk through an end to end demo below

Inventory Management Intro

Here’s another model download also

Inventory Management

These showcase many inventory analysis and the many nuances of working with inventory data. Hopefully this can get you started.

[sam.mckay]

Here’s another example of inventory analysis as well

[djpejsen]

Hi Sam
Thank you for the showcases, but I have not find a solution.

[djpejsen]

This is my datamodel

[djpejsen]

I want a report like this :
Show BOM item and Required qunatity per date.
The table “factOn-hand Inventory” has “Total Available Qty.” per item.

I want to reflect in the report, the item stock out per start date or Year- Week number.
The On-hand allocation must allocate the on-hand by FIFO principle (start date)

[sam.mckay]

Okay let’s work through this. I’m still not totally understanding the requirement here and I’ve read through this about 8 times now.

Have you attempted any formula to get the answer so I can review it?

Do we start with Total Available Qty? and then where to from there?

I’m sorry I’m just not getting this right now as not familiar with this exact data setup and unique requirement.

Is what you are currently showing a PowerPivot model and pivot table? Or is this just a table?

How are you looking to represent this ultimately?

Sorry a few questions, I want to assist here but struggling to put all the pieces together currently.

Chrs
Sam

[djpejsen]

Hi Sam

Thanks for taking time to support on this.

In the factOn-hand Inverntory table, we have a column “total availble quantity” that show the on-hand quantity for each row record (Bom Item)

In “factProductionStart” there are multi row records for each Bom items. Every row have a Production order start date, BOM item and a required quantity for each BOM item.
I want to allocate the on-hand inventoy for each row (BOM item) in factProductionStart table, based on the Production start date and required quantity.
There is also a Index column in factProductionStart I will use this index in case of the start dates are the same date for multi Production orders

Let assume that BOM item “A” have On-hand quantity of 500 pieces in factOn-hand inventory table
I want use this on-hand quantity for item “A” as the first step in order to allocate available oh-hand to all Item “A” in factProductionStart

Starting the allocation to Production order with oldest start date (FIFO principal) , subtract the required quantity from the onhand inventory quantity for that particular item .
Then go to the next Production order start date or index, and allocate the on-hand inventory to Production order number 2
But I need to subtract the required quantity for Production order number 1, from the on-hand qty, before subtract the required quantity for Production order number 2

Example:
Production order 1 - BOM Item “A”
Production order with oldest Start date = 01/03/2018
Required Qty = 100 pcs.
On-hand inventory= 500 pcs.
Allocation = 500-100 = 400 pcs left at Inventory

Production order 2 - BOM Item “A”
Production start date = 02/03/2018
Required Qty = 100 pcs.
On-hand inventory = 400 pcs. due to I have already allocated 100 pcs. to previous producton order, so my available inventory is now 400 pcs
Allocation = 400-100 = 300 pcs left at Inventory

Production order 3 … 300-100 = 200
Production order 4 … 200-100 = 100
Production order 5 … 100-100 = 0
Production order 6 … 0-100 = -100
And onwards, until I have allocated all production orders.
The number with bold font are “Total Available inventory after allocation”, should be use for the next Production order as “Total Available inventory before allocation” in the sequence

When the “on-hand inventory available after allocation” start to show quantity less than 1 pcs, then I can easliy see on which date or week number I have a stock out.
.

Hope this give more clarification

/ Jan

[sam.mckay]

Ok I think I get this now…a really interesting example (and challenging one).

I would like to work up an example of this myself with some demo data and then send through a good overview of how to do this, then also create a video out of it.

In the meantime…

This is how I believe you would do this.

First you would SUM( Sum of Total Quantity )

When you use this sum within the current context of BOM item from the lookup table dimAllItems, it will always return the full total that we want.

When you place start date into the context of the calculation, you will still receive the full value every row which is good.

Then we need to calculate the cumulative total based on dates of the Required Quantity column from the Production table.

This way we can get a cumulative total and subtract this from the full total, giving the allocation and then the amount remaining to be allocated.

This is the logic.

I will have to either work up some demo data myself, or if you can send me your model in Power BI I can likely work up some images of this faster.

Let me know what you think

[djpejsen]

Hi Sam

Thats sounds great.

I will prepare some demo data and forward to you

[sam.mckay]

Ok great that would be helpful. Chrs

[djpejsen]

I am not allowed to provide you the real data from my company, so I have to change naming for the items.

[sam.mckay]

Totally understand, I wouldn’t recommend that. Just a small demo set is all that required so that we can discuss the concepts and patterns required to solve this, then you can just sub in the technique into your own model.

[Jeffb]

Hi Sam, this scenario is very interesting something I’ve been thinking about myself. I know this is from a couple of years ago but do you have a solution for the above problem from djpejsen?