Inventory based on Expiry Dates (FIFO)

[Oros]

Would it be possible to get an inventory summary of products with QTY based on specific expiry dates, according to first in, first out (FIFO)?

There are 2 tables:

1. INVENTORY TABLE (current inventory)
2. RECEIVING TABLE (QTY received based on expiry dates)

Power Bi result table must show:

INVENTORY with EXPIRY DATES (on assumption that the future dates are the ones being retained)

Thanks.

[Nhk22]

H @Oros i, im interested to know what is the solution for this ? thanks for sharing

[v-yingjl]

Hi @Oros ,

Not clear with your expected output, some questions:

1. Under FIFO logic, why the APPLE INVENTORY in the expected output table cut to 2 rows with 50 inventory each, but BANANA still retain 125 inventory with one row
2. What did 'on assumption that the future dates are the ones being retained' mean

Perhaps you can consider sharing more details about this issue.

Best Regards,
Community Support Team _ Yingjie Li

[Oros]

Hi @v-yingjl ,

Sorry if it's not clear.  The missing qty is the qty sold.  So if you have 100 apples in the inventory but you received originally a total of 140 qty, Power Bi result must show that the remaining 100 are the 50+50 with the most future dates.

Example, today the inventory is

40 (expiry date of October 2022)

50 (expiry date of November 2022)

50 (expiry date of December 2022)

if tomorrow, I sold 50, my remaining inventory should be as follow: 

40 (expiry date of November 2022)

50 (expiry date of December 2022)

Because the system sold the earliest expiry date of 40 qty (from October 2022 expiry) + 10 qty (from expiry date of November 2022)

Thanks.

[amitchandak]

@Oros , refer if this blog can help

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

[Oros]

Hi @amitchandak ,

Thanks for your reply.

Unfortunately this is not the one that I am looking for.  Thanks again.