Forum Discussion
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! 🙂
2 Replies
- amitchandakSuper User
couchserver , Refer if solution from RADACAD can help
https://radacad.com/dax-inventory-or-stock-valuation-using-fifo
- couchserverFrequent Visitor
amitchandak Thanks for the hint. I already know this approach and edited and use it in parts of my report but I couldn't find a way yet to tailor it to this specific need descirbed in my post.