Aging of the items on the stock & outside of stock

[IvanS]

Hello,

I would need your advices and best practices how to calculate aging of the items on Item Ledger table. The task is clear - I need to calculate aging of the items which are

1. Currently on the stock

2. Were on stock in the past but there is no quantity left on stock

My table looks like below:

Entry_Ref.	Posting date	Entry_Type	Item_No	Location_Code	Quantity
1	1.1.2022	Purchase	AA-1	LONDON	5
2	10.1.2022	Sale	AA-1	LONDON	-5
3	15.1.2022	Purchase	BB-2	PARIS	3
4	17.1.2022	Transfer	BB-2	PARIS	-1
5	17.1.2022	Transfer	BB-2	LONDON	1
6	20.3.2022	Sale	BB-2	PARIS	2
7	1.4.2022	Purchase	CC-3	PARIS	10
8	1.5.2022	Purchase	DD-4	PARIS	7
9	15.5.2022	Sale	CC-3	PARIS	-5
10	1.6.2022	Transfer	CC-3	PARIS	-2
11	1.6.2022	Transfer	CC-3	LONDON	2
12	1.6.2022	Sale	DD-4	PARIS	-7

I would like to calculate:

1. Inventory Flag - which Item_No is still on stock in any warehouse and which is out of stock.

2. If still on stock, then to calculate aging based on Purchase posting date and today's date.

3. If not on stock anymore, then to calculate aging based on Purchase posting date and last Sale posting date.

I am more than happy if this will be new calculated table derived from the previous table. Example:

Item_No	Inventory Flag	Quantity	Aging in days
AA-1	Out of stock	0	9
BB-2	On stock	1	difference between 15.1 and today's date
CC-3	On stock	5	difference between 1.4 and today's date
DD-4	Out of stock	0	31

Hopefully, my explanation is clear enough.

Thank you for any help. 

[johnt75 · Accepted solution]

You can create a table like

Stock summary = GENERATE(
    VALUES( 'Table'[Item_No] ),
    VAR itemNo =
        SELECTEDVALUE( 'Table'[Item_No] )
    VAR currentStock =
        CALCULATE( SUM( 'Table'[Quantity] ) )
    VAR lastPurchaseDate =
        CALCULATE( MAX( 'Table'[Posting date] ), 'Table'[Entry_Type] = "Purchase" )
    VAR lastSaleDate =
        CALCULATE( MAX( 'Table'[Posting date] ), 'Table'[Entry_Type] = "Sale" )
    VAR dateToCompare =
        IF( currentStock > 0, TODAY(), lastSaleDate )
    VAR aging =
        INT( dateToCompare - lastPurchaseDate )
    VAR inventoryFlag =
        IF( currentStock > 0, "On stock", "Out of stock" )
    RETURN
        ROW(
            "Quantity", currentStock,
            "Inventory flag", inventoryFlag,
            "Aging", aging
        )
)

[IvanS]

Oh my god! Thank you so much! 🙂

Just please correct the following part of formula as the purpose is to find first PurchaseDate (MIN) and not last PurchaseDate (MAX)

VAR firstPurchaseDate =
        CALCULATE( MIN( 'Table'[Posting date] ), 'Table'[Entry_Type] = "Purchase" )