Assign dates in FIFO logic for a certain number of rows

Question

Hi I have an inventory information like this:

1. Stock in table: will have Stock In date - Model - Product ID

- where the product ID is unique to each product under the same SKU

2. Stock out table: will have Sales date - Model - Number of product sold, so let’s say on day 1 I can sold 5 units of product A

It’s a first in first out problem, I want to assign the sales out date to each of my Product ID. For example, if in day 1, I have stocked in 5 items = 5 ID of model A, and O’ve sold 3 model A items, then 3/5 Product ID of model A will be assigned the sales date as day 1. If next day I only sell 1 product then the 4th item stocked in day 1 will have sales date of day 2

If I sell no item then no Product ID get assigned any sales date

please help me thank you so much

KaneG · Answer

That data is identical to the second link that I posted above, where I also posted a solution in order to look back. I'm at a loss to see what's different. Can you please be more specific as to what needs to change?

PhuongTran52 · Answer

Hi @KaneG

Thank you for asking, I have my data as below, this is a cut of one SKU with low volume

I have my Sales out data, Stock in data, and detailed Stock In with Unique ID, and my desired output. I do this manually, but with more SKU and order quantities I am really struggle

Thank you for asking again...

StockAge FIFO Input example.xlsx

KaneG · Answer

Do you have a sample of what you have so far?