Forum Discussion
On hand Inventory Shipments (FIFO)
Hello, This is my first post so I want to thank in advance for any help received.
What I'm trying to accomplish is to display the separate shipments that make up current inventory levels by product using FIFO method. This means that if I have, for instance, 100 pcs on hand and I have received 6 shipments of 20 pcs each, the current inventory would be formed from the last 5 shipments received.
I have a table that shows current inventory quantities and a separate table with the shipments by date. The shipments table has the information I need but it lists all the shipments received, however, I need for it to only display enough shipments to cover the current on hand inventory quantity and, to start counting from the last shipment back. I hope I was able to explain myself. Here are some pictures that might help:
This table shows all shipments received of Item "A" (from "Shipments" table):
However, currently there's ony 5,852,058 lbs on hand (from "Inventory" table):
So, In this case the expected result I seek would be:
I further clarification is needed please let me know, once again thanks for any help.
Hi andresVISA ,
Please try:
Measure = VAR _a = ADDCOLUMNS ( ALL ( 'Shipments' ), "sum", SUMX ( FILTER ( ALL ( 'Shipments' ), [ItemCode] = EARLIER ( Shipments[ItemCode] ) && [Itemname] = EARLIER ( Shipments[Itemname] ) && VALUE ( RIGHT ( [Shipment date (Qtr)], 2 ) & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 ) ) >= VALUE ( RIGHT ( EARLIER ( [Shipment date (Qtr)] ), 2 ) & RIGHT ( LEFT ( EARLIER ( [Shipment date (Qtr)] ), 2 ), 1 ) ) ), [Qty] ) ) VAR _b = MAXX ( FILTER ( _a, [Shipment date (Qtr)] = MAX ( 'Shipments'[Shipment date (Qtr)] ) ), [sum] ) VAR _c = MAXX ( FILTER ( 'Inventory', [ItemCode] = MAX ( 'Shipments'[ItemCode] ) && [Itemname] = MAX ( 'Shipments'[Itemname] ) ), [ On hand ] ) VAR _d = MINX ( FILTER ( _a, VALUE ( RIGHT ( [Shipment date (Qtr)], 2 ) & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 ) ) = MINX ( FILTER ( _a, [ItemCode] = MAX ( 'Shipments'[ItemCode] ) && [Itemname] = MAX ( 'Shipments'[Itemname] ) && [sum] <= _c ), VALUE ( RIGHT ( [Shipment date (Qtr)], 2 ) & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 ) ) ) ), [Shipment date (Qtr)] ) RETURN SWITCH ( TRUE (), _c >= _b && MAX ( 'Shipments'[Shipment date (Qtr)] ) <> _d, MAX ( 'Shipments'[Qty] ), _c >= _b && MAX ( 'Shipments'[Shipment date (Qtr)] ) = _d, MAX ( 'Shipments'[Qty] ) + _c - _b, BLANK () )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
andresVISA I've done quite a bit of work in this area perhaps these will get you started. Would need a lot more information to be more specific:
Order Fulfillment - Microsoft Power BI Community
Hi Greg, thanks for the quick reply and for sharing the links of your work, it looks very interesting although I have to admit that I´m not sure how to implement it to my solution...you mentioned that you need more information, what can I provide you with?
andresVISA Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi andresVISA ,
Please try:
Measure = VAR _a = ADDCOLUMNS ( ALL ( 'Shipments' ), "sum", SUMX ( FILTER ( ALL ( 'Shipments' ), [ItemCode] = EARLIER ( Shipments[ItemCode] ) && [Itemname] = EARLIER ( Shipments[Itemname] ) && VALUE ( RIGHT ( [Shipment date (Qtr)], 2 ) & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 ) ) >= VALUE ( RIGHT ( EARLIER ( [Shipment date (Qtr)] ), 2 ) & RIGHT ( LEFT ( EARLIER ( [Shipment date (Qtr)] ), 2 ), 1 ) ) ), [Qty] ) ) VAR _b = MAXX ( FILTER ( _a, [Shipment date (Qtr)] = MAX ( 'Shipments'[Shipment date (Qtr)] ) ), [sum] ) VAR _c = MAXX ( FILTER ( 'Inventory', [ItemCode] = MAX ( 'Shipments'[ItemCode] ) && [Itemname] = MAX ( 'Shipments'[Itemname] ) ), [ On hand ] ) VAR _d = MINX ( FILTER ( _a, VALUE ( RIGHT ( [Shipment date (Qtr)], 2 ) & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 ) ) = MINX ( FILTER ( _a, [ItemCode] = MAX ( 'Shipments'[ItemCode] ) && [Itemname] = MAX ( 'Shipments'[Itemname] ) && [sum] <= _c ), VALUE ( RIGHT ( [Shipment date (Qtr)], 2 ) & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 ) ) ) ), [Shipment date (Qtr)] ) RETURN SWITCH ( TRUE (), _c >= _b && MAX ( 'Shipments'[Shipment date (Qtr)] ) <> _d, MAX ( 'Shipments'[Qty] ), _c >= _b && MAX ( 'Shipments'[Shipment date (Qtr)] ) = _d, MAX ( 'Shipments'[Qty] ) + _c - _b, BLANK () )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
