Help figure out how much of each lot / record was consumed up to a known number FIFO

[lucyjohnson]

Good morning, 

I have a set of goods receipts and total consumed. I need to figure out how much is consumed from each lot up to a total known number (basically FIFO method of inventory tracking). 

The yellow data is what's known / the starting point. The green is the "answer" I did manually, but I need Alteryx to figure that out for me. Column G is what's used by each site. In the example, site 1800 used a total of 293934 units and site 1950 used a total of 273000. Now I need to know how much of each lot each site used / how much "qty on hand" was consumed from each lot. Lots are in chronological order (2024 / 09 is September 2024). Lot 1 will always be used first, then 2, then 3, until we get to the 293934, for example.

I cannot figure out how to do this cleanly when I could have 1 lot or 6 or 3, etc. All depends on how long the inventory is kept at each location.

Please help, I've spent two days on this :) 

 

[OTrieger]

@lucyjohnson 
FIFO, First In First Out so you know the following.
If Used is bigger than 1st lot then the whole lot was used.

If Used is bigger than 1st + 2nd lot then the whole 2nd Lot was used too, if the total used is smaller then the calculation will be (Lot 1 + 2) - Used.

There is a one tool that cane help you, Running Total, it will give you the total that you need for the calculation.

Use the Running Total on the Lots amounts and compare it to the Total used.

[Qiu · Accepted solution]

@lucyjohnson 
I use a Running total and Multi-row formula tool to give a try as below.

[lucyjohnson]

Thank you! This worked. I added one more part to the last tool which checks if the lot number is 1 and the qty on hand is more than total used / issued (new in your example) then to use the total used / issued. And it's working for my real data set. THANK YOU

elseif [Lot Number] = 1 and [Qty on Hand] > [Total Used / Issued] then [Total Used / Issued]

[Qiu]

@lucyjohnson 
Glad to be any help.