Calculating and Tracking FIFO Inventory and Costs

[saranicole2695]

Hello. I have a business group that needs to track inventory and costs using FIFO methodology. They are tracking widgets consumed during the course of business. They buy inventory as needed throughout the month then at the end of the month they will enter the number of widgets used during the month. Multiple cost layers/inventory rows per month and a single outbound per month for each widget type.

I need to be able to track each purchase/cost layer individually and calculate the monthly usage cost for each product using FIFO costing. I followed the steps found in this post https://radacad.com/dax-inventory-or-stock-valuation-using-fifo but I am having some issues. 

Here is an example PBIX file of what I currently have. https://drive.google.com/file/d/1jnyC3rkeLxAruJkfO0Jez88SrGUWJP-b/view?usp=sharing The Activity Tab is the details by layer and transaction. The Desired Layout tab is how the business wants to view the data. Data is collected from the business using Forms and Power Automate to add the data to an excel sheet in Teams. The Purchase and Used tables are that data. The Inventory table is an appended table of the two.

• Activity Tab
  • Usage Expense Calculation: When there are more than one usage on a cost layer that does not complete the cost layer. See Products IN0012 and IN0017. The Usage for December calculates correctly but the Usage for January is not. 
    • 
    • See Product IN0016. The first 2 usage records do calculate correctly but the third does not.
  • Remaining Balance Calculation: I think this one is good but if someone can review it that would be great. I can't find where I found this formula.
• Desired Layout
  • Period Totals: The business wants to see totals for each month (relative date: Last 1 Calendar Month or This Calendar Month). I could use some help with these calculations.
    • Beginning Inventory Value: Total beginning inventory value and the beginning of the selected period.
    • Period Added Value: New Purchases for the Period - I'm good with this one.
    • Period Total Inventory Value: Beginning Value plus new Purchases
    • Ending Inventory Value: Beginning Value plus new Purchases minus period Usage value.
  • Table layout
    • I'm struggling with how to pull the usages into the cost layer record and not have them a separate record.

Thank you for your assistance!

[Habitat]

Hi @saranicole2695 ,

I have a similar problem, have you made any progress?

Besides, I will update here if I have any ideas.

Hope we could solve this problem.

Kind regards,

Morty

[saranicole2695]

Not yet. Took a step back from it hoping for some help. If I do I will post.