Forum Discussion

couchserver's avatar
couchserver
Frequent Visitor
2 years ago

Struggle with Inventory Valuation (FIFO) and DAX

 

Hello all! Given the data from the screenshot above, could somebody help me to calculate the amount of products from the currently remaining quantity that has a connected shipment number (meaning where the field "shipment" is empty). This number is highlighted yellow. What makes it difficult for me is that the inventory valuation method is FIFO which means that always the first incoming products are the first to leave the warehouse again. Also it is important that the calculation is always based on the indivual products in the the individual warehouses as the highlighted rows in the screenshot shows. The goal is to display that number in a table for each product in each warehouse 

Example:

Currently for Product A in Warehouse 1 there are 14 units left of which only 9 have a connected shipment - what is needed is a calculation for that exact column "quantity with shipment".

I think the solution is found if a measure or caclulated column checks the current quantity on hand (lets say X) and then goes through the latest X incoming products (which ultimately form the stock on hand due to FIFO). The latest incoming products can be identified with move ID that is created for each new stock move. The amount of products of these incoming products with a allocated shipment is what I need to know - I just didnt find a way to formulate the DAX correctly yet.

Thank you all in advance for any support! 🙂