Forum Discussion
DAX for FIFO quantities
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...
Thank you so much for making the effort!
In this solution the sold quantities are not reducing the oldest stock first. In your posted example all of the stock from August should have been used up.
If I switch InventoryAcquired sign to <=MIN, all other SKU examples are fixed except for XYZ with its multiple stock dimensions. Not sure what to make of this..
If you have a spare moment at some point, I'd very much appreciate it if you could doublecheck.
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...
Thanks a lot, this is nice and clean!
egle_p you're welcome. Glad I could help!
Hi littlemojopuppy good afternoon.
Quick question, the VAR of
VAR InventoryAcquiredPM , what is it purpose ?, sorry I'm new both in power Bi and FIFO calculaitons, I'm having trourble understanding the variables within the FIFO sold calcualtion, could you explain those ?Btw , amazing and clean code.Cheers!Herig9596 it's been two years since I did this but I'll try...
Inventory valuation is basically done one of a couple different ways:
- Specific identification...this is usually reserved for non-commodity/mass produced things such art works
- Average Cost...calculate the weighted average cost of inventory.
- LIFO - Last In First Out. When inventory of goods are sold, the value of on hand inventory declines based on the inventory most recently placed into inventory. Think of reaching towards the front of the shelf.
- FIFO - First In First Out. When inventory is sold, the value decreases by the value of inventory that has been on the shelf the longest. Like reaching towards the back of the shelf.
The whole calculation is about how much inventory is on hand "right now" for the month in context. Plain language explanation as best I remember or can figure out now...
- InventoryAcquired - total amount acquired from the beginning of time through the end of the month in context
- InventoryAcquiredPM - total amount acquired from the beginning of time before the current month in context (InventoryAcquired - InventoryAcquiredPM will give you amount acquired in the current month)
- InventorySold - the amount of inventory sold. It seems odd to me that I used REMOVEFILTERS() on the calendar, but it seems to work.
You see that there were 12 units of A sold in February 2022. The calculation determines that 11 of those units came from the inventory acquisition from August 2021 and one unit from September 2021. First units in were the first to be sold.
Quantity of inventory on hand at any point in time is the total quantity ever bought - total quantity ever sold. At the time, this was the easiest way I could think of to calculate that with the given inputs. There are things I might change looking at this again (maybe?)
Reference for LIFO vs. FIFO: https://www.investopedia.com/articles/02/060502.asp
Hope this helps...
- Thank you very much! This helps me a lot, really appreciate the time to respond!
