Forum Discussion

Jonas_Holm's avatar
Jonas_Holm
Helper I
2 years ago

Inventory age - FIFO

Hello everyone,

 

I have tried several methods, but I can't seem to find a DAX formula, that can calculate for a selected date. e.g. in this example, I have tried to filter on the 31st of July

So I need a dynamic formula, that shows the open quantity for <1 month, and then another for >6 month and >1 month.

And if I select another date, it calculate itself for the different warehouse ageings.

Remaining quantity and Warehouse age is only to show the result, only posting_date, Source_type, Item_no and Quantity is in my table.

The principal I'm using is FIFO.

 

Can anyone help with this?

Measure 1:

WH >1m =
(CALCULATE([WH Value CY],ALL('Date'[Date]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-1,MONTH)) - [WH Value CY])*-1
 
Measure 2:

WH Value CY =

VAR LastVisibleDate =
    MAX('Date'[Date])
VAR FirstVisibleDate =
    MIN('Date'[Date])
VAR LastDateWithSales =
    CALCULATE(
            MAX('Value Entry'[posting_date]),
            REMOVEFILTERS()
    )
VAR Result =
    IF(FirstVisibleDate <= LastDateWithSales,
        CALCULATE([Inventory value], 'Date'[Date] <= LastVisibleDate
        )
    )

RETURN Result


Measure 3:

Inventory value =
CALCULATE(
    SUM('Value Entry'[CostAmountActualNew2])) + SUM('Value Entry'[CostAmountExpected3])
 
Quantity and inventory value is the same.
Need Help