Forum Discussion
DAX for FIFO quantities
Hello! I've been down this FIFO rabbit hole before so thought better turn to experts.
Need to tweak the very helpful inventory valuation logic https://radacad.com/dax-inventory-or-stock-valuation-using-fifo to give me a column of sold quantities for each buy row as per FIFO logic. Cumulative columns should be the same (?), just the FilteredFIFOTable and Result that need rearranging, I expect.
Many thanks!
Hi egle_p
Somewhere in my head, looking at the data you provided, I thought you were going for LIFO, despite you clearly saying FIFO. My apologies... 😕This is what you were after...
12 Replies
Hi egle_p
You're going to need to provide some sample data and the DAX of what you've got so far...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150egle_p I've had a monster bad week at work. I apologize for not looking at this...will try to do so tomorrow 🙂
littlemojopuppy Thank you for remembering 🙂
I've figured most of it out - got too stuck on the filtered tables before. But one issue remains - rows with same arrival date need to be ordered by some other column as well (can't group them in the real data). Best would be to start from smallest quantity. I've given it a try and will continue to search in the original shared file.
Hi! littlemojopuppy sorry - as the link included a pbi file, I got careless 🙂
My work is here https://1drv.ms/u/s!Ak-sMmW0cEa0g7hq8SYtWGVvwceDGQ?e=A0ooSi
It includes a column of expected results (sold quantities in the order of last arrival in stock, by SKU and stock location), 4 calculated cumulative columns that look correct and the final FIFO sold calculation that I'm unable to wrap my head around..
There will always be one sale row per SKU per location, so the previous cumulative sell is constantly 0 but I expect I can change that for better efficiency myself.
