Hi, I need some assistance please.
We import products that are then distributed to customers. The list that is currently used (see attached example), shows the quantity imported (Qty) and the duty paid (Duty).
When Items are sold (Used), it is indicated on the sheet by adding a row with the status of Used. All Used items must be equal to the Imported items based on Line number, Product code, Invoice Number, BE date and BE number. The distributed (Used) items are not always equal to the Import qty, and it might take 2 or more lines to match up to the Import quantity.
For row 13 there were 560 items imported, but nothing used or distributed yet.
Products must be distributed (Used) chronologically, so first in, first out.
The problem is that this list can consist of 7 000 or more rows and to find the next available product becomes a challenge.
Is there a way to perhaps filter out/remove from view, where the Import and Used lines add up to the same quantity for the same Line Number, Product Code, Duty, Invoice number, Customs BE date, Customer BE Number and Qty?
We also need to calculate the pro-rata duty for the Used items (see Column E > used items only)
Any assistance on how to manage this in Power Query would be greatly appreciated.
Hi Corrie,
The FIFO problem is more complicated than you described, for example, units sold may take prices from 2 imported items, if the quantity remaining from purchase is not enough to fill the order, so the rest of the quantity needs to be taken from the next purchase.
Try this link: https://community.powerbi.com/t5/Desktop/Power-Qurey-for-FIFO-Stock-Calculation/td-p/312235
At the end of the topic you will find a download, hope it will work for your situation.
Hi Catalin,
You're quite right, it is more involved.
Thank you for the shared link. I will have a look and see how I can apply it in our scenario.
Take care!
