DAX for FIFO quantities

[egle_p]

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!

[egle_p]

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.

[littlemojopuppy]

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#M6071...

[littlemojopuppy]

@egle_p I've had a monster bad week at work.  I apologize for not looking at this...will try to do so tomorrow 🙂

[egle_p]

@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.

[littlemojopuppy]

Hi @egle_p 

Sorry for the delay with this...I had a monster bad week at work.  😞

I took your table and refactored it into a data model based around inventory transactions.  Here is a solution that doesn't involve calculated columns.  Overall, I think it's much simpler than what you had.

Hope this helps!

[egle_p]

Hi @littlemojopuppy 

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.

[littlemojopuppy · Accepted solution]

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...

[Herig9596]

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!

[littlemojopuppy]

@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:

1. Specific identification...this is usually reserved for non-commodity/mass produced things such art works
2. Average Cost...calculate the weighted average cost of inventory.  
3. 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.
4. 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...

[Herig9596]

Thank you very much! This helps me a lot, really appreciate the time to respond!

[egle_p]

Thanks a lot, this is nice and clean!

[littlemojopuppy]

@egle_p you're welcome.  Glad I could help!