Calculation of age Days based on FIFO Inventory method

Question

Dear Experts,&nbsp;amitchandak&nbsp;&nbsp;Greg_Deckler&nbsp;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.),&nbsp;First Table (Stock) include the closing stock of a particular item:DateMaterial No.Quantity PC30-Apr-23&nbsp; &nbsp; 10001&nbsp;&nbsp;&nbsp; 72,000.00&nbsp;Second Table (PurchaseTable) contains the purchase history of this item:Material No.Date of Purchase&nbsp;Quantity PC&nbsp;1000131-Mar-14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000.001000115-May-15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,000.001000123-Sep-18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,000.001000103-Oct-20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,000.001000108-Jun-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,000.001000130-Sep-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,000.001000131-Mar-23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,000.001000115-Apr-23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,000.00&nbsp;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.&nbsp;Desired Results should be like this:Material No.Date of Purchase&nbsp;Quantity PC&nbsp;Age in DaysStock Balance1000131-Mar-14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000.00&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (46,000.00)1000115-May-15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,000.00&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (21,000.00)1000123-Sep-18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,000.00&nbsp; &nbsp; &nbsp;1691&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,000.001000103-Oct-20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,000.00&nbsp; &nbsp; &nbsp;950&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,000.001000108-Jun-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,000.00&nbsp; &nbsp; &nbsp;337&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31,000.001000130-Sep-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,000.00&nbsp; &nbsp; &nbsp;223&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 42,000.001000131-Mar-23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,000.00&nbsp; &nbsp; &nbsp; 41&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 51,000.001000115-Apr-23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,000.00&nbsp; &nbsp; &nbsp; 26&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 59,000.00

greg_deckler · Answer

geemian93&nbsp;See if these help:
Delivery Dates - Microsoft Power BI Community
Order Fulfillment - Microsoft Power BI Community
&nbsp;
If not, I'll try to take a closer look when I have some spare time.

geemian93 · Answer

Greg_Deckler&nbsp; Thanks for your response.&nbsp;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".&nbsp;Apprecite if you can spare some time to look into this.&nbsp;RegardsWaseem

geemian93 · Answer

Greg_Deckler&nbsp;Appreciate if you can look into this please.

Forum Discussion

Calculation of age Days based on FIFO Inventory method

3 Replies

Related content

Inventory and Stock balance calculation

shortcuts - Refresh Method

Calculate a Running Daily Inventory

Inventory Forecast

Inventory Management Dashboard

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