Request for Assistance with FIFO Inventory Ageing

[Ilgar_Zarbali]

Hello,

I’m currently working on a Power BI report to calculate inventory ageing quantities using the FIFO method. I believe you might have some helpful DAX patterns for this type of calculation. If you have any relevant resources or references, I would greatly appreciate it if you could share them with me.

Thank you in advance for your support!

Best regards,

[v-xiaoyan-msft]

Hi  @Ilgar_Zarbali

Of course! Here are some additional DAX patterns that can help you implement the FIFO method in Power BI for calculating inventory ageing quantities:

1. Calculate Inventory Balance: This measure calculates the running balance of inventory by considering both incoming and outgoing quantities.

   InventoryBalance = 
   CALCULATE(
       SUM('Inventory'[QuantityIn]) - SUM('Inventory'[QuantityOut]),
       FILTER(
           ALL('Date'),
           'Date'[Date] <= MAX('Date'[Date])
       )
   )

2. FIFO Inventory Value: This measure calculates the value of the inventory using the FIFO method by considering the cost of the oldest inventory first.

   FIFOInventoryValue = 
   SUMX(
       FILTER(
           'Inventory',
           'Inventory'[Date] <= MAX('Date'[Date])
       ),
       'Inventory'[Quantity] * 'Inventory'[UnitCost]
   )

3. Inventory Ageing Buckets: This measure categorizes inventory into different ageing buckets (e.g., 0-30 days, 31-60 days, etc.) based on the FIFO method.

   InventoryAgeing = 
   SWITCH(
       TRUE(),
       DATEDIFF('Inventory'[Date], TODAY(), DAY) <= 30, "0-30 Days",
       DATEDIFF('Inventory'[Date], TODAY(), DAY) <= 60, "31-60 Days",
       DATEDIFF('Inventory'[Date], TODAY(), DAY) <= 90, "61-90 Days",
       "90+ Days"
   )

4. FIFO Cost of Goods Sold (COGS): This measure calculates the cost of goods sold using the FIFO method.

   FIFOCOGS = 
   SUMX(
       FILTER(
           'Sales',
           'Sales'[Date] <= MAX('Date'[Date])
       ),
       'Sales'[Quantity] * LOOKUPVALUE('Inventory'[UnitCost], 'Inventory'[Date], EARLIER('Sales'[Date]))
   )

   For more you may refer to :DAX Patterns

   Best regards.
   Community Support Team_Caitlyn

[Ilgar_Zarbali]

Thank you so much! I have applied these patterns to my FIFO-based aging calculations. I truly value your support.