I have been trying to solve this but hitting a wall.
I have order data that has order date as well as SKU's produced each week in columns as dates.
I want to find out the order fulfillment date i.e. if the Qty needed was 20 and the production in 2nd week (5/23/2022) was 18 and 6/6/2022 was 45 then qty 18 was fulfilled from 5/23/2022 and remaining 2 from 6/6/2022. Therefore, 6/6/2022 is the fulfillment date. Also, the remaining inventory is total production on all dates - qty needed.
Note - Different orders can have similar SKUs
See attached
Thanks!
@yogi_789 I believe you could get to a solution by transposing the data and using running totals. See the attached workflow for reference.
Hi @yogi_789 see the attached workflow and screenshot to find both the fulfillment date as well as the remaining quantity in stock.
Thanks all! The solutions posted do not reduce the reserves size. e.g., if n units were allocated to SKU 1 on xyz date then those n units should not be available to the next SKU. So what will happen eventually is that if 200 units for a SKU in one order are needed and 200 are available on 5/16/2022, then the fulfillment date would be 5/16/2022. If next order requires 50 units of same SKU then the next available date should be next date on which 50 or less production is available (as 200 produced on 5/16/2022 are already used in a previous order)
Hi @yogi_789,
See the attached workflow for an additional few tools that create a running total of the stock after an order is fulfilled.