Forum Discussion
On hand Inventory Shipments (FIFO)
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.
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.Sounds good, here are the reference tables for my example simulating the situation:
"Inventory" table:
ItemCode Itemname On hand 16431 A 5,852,058 "Shipments Table"
ItemCode Itemname UOM Shipment date (Qtr) Qty 16431 A LB Q3/20 220,460 16431 A LB Q1/21 2,469,152 16431 A LB Q2/21 1,499,128 16431 A LB Q3/21 925,932 16431 A LB Q4/21 2,116,416 16431 A LB Q1/22 2,072,324 16431 A LB Q2/22 3,747,820 And this is the table of the expected result:
ItemCode Itemname UOM Quarter Qty 16431 A LB Q1/22 2,104,238 16431 A LB Q2/22 3,747,820 The logic to get to the result would be to add up Shipment quantities starting from the latest shipment until you "cover" exactly the On Hand quantity of the respective item, then only display the shipments involved in the results table.
Hope this helps, please don't hesitate if you need more data.