Calculation of age Days based on FIFO Inventory method

[geemian93]

Dear Experts, @amitchandak  @Greg_Deckler 

I am looking for the help to calculate aging days based on the FIFO method of inventory from the two tables (Both tables have relationship based on Material No.),

First Table (Stock) include the closing stock of a particular item:

Date	Material No.	Quantity PC
30-Apr-23	10001	72,000.00

Second Table (PurchaseTable) contains the purchase history of this item:

Material No.	Date of Purchase	Quantity PC
10001	31-Mar-14	25,000.00
10001	15-May-15	22,000.00
10001	23-Sep-18	15,000.00
10001	03-Oct-20	15,000.00
10001	08-Jun-22	11,000.00
10001	30-Sep-22	9,000.00
10001	31-Mar-23	8,000.00
10001	15-Apr-23	13,000.00

I wanted to apply the logic that only calculate age days from today date (11-May-23)
minus the last purchase quantity from the closing quantity (i.e. 72,000 - 13,000 pc) and so on untill the closing stock is completely consumed then leave the rest of the purchase histroy age days to zero.

Desired Results should be like this:

Material No.	Date of Purchase	Quantity PC	Age in Days	Stock Balance
10001	31-Mar-14	25,000.00	0	(46,000.00)
10001	15-May-15	22,000.00	0	(21,000.00)
10001	23-Sep-18	15,000.00	1691	1,000.00
10001	03-Oct-20	15,000.00	950	16,000.00
10001	08-Jun-22	11,000.00	337	31,000.00
10001	30-Sep-22	9,000.00	223	42,000.00
10001	31-Mar-23	8,000.00	41	51,000.00
10001	15-Apr-23	13,000.00	26	59,000.00

[geemian93]

@Greg_Deckler Appreciate if you can look into this please.

[Greg_Deckler]

@geemian93 See if these help:

Delivery Dates - Microsoft Power BI Community

Order Fulfillment - Microsoft Power BI Community

If not, I'll try to take a closer look when I have some spare time.

[geemian93]

@Greg_Deckler  Thanks for your response.

I had a look into the proposed article, however it accumulates the item quantity with earliest possible delivery date.

The problem that I am facing is actually simpler since I just wanted to assign age (in days from current or specific date) deducting the most recent purchased quantity from the closing quantity of particular item until the closing quantity reaches "Zero" then leave rest of the purchase history line items age days to "0".

Apprecite if you can spare some time to look into this.

Regards

Waseem