Calculate Age Days from the purchase history of an item deducting the current on hand Stock (FIFO)

[geemian93]

Dear DAX Experts, @amitchandak  @lbendlin @Greg_Deckler @Ritaf1983 

I am looking for a 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 just wanted to assign age (in days from current or specific date, 11-May-23 in this example) deducting the most recent purchased quantity from the closing quantity of particular item (i.e. 72,000 - 13,000 pc) until the closing quantity reaches "Zero" then leave rest of the purchase history line items age days to "0".

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

[lbendlin]

Does it have to be DAX or can it be done in Power Query?

[geemian93]

@lbendlin  Thanks for your reposne.
I am working in power BI right now, but it can ben done in Power Query as well.
Appreciate if you can suggest the soluion in Power Query also would be great.

[geemian93]

@lbendlin  Would you please suggested your solution?

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc+xCsMwDATQXzGefUUn1eCMWQOhQ8eQoZSuJZRmyN/HNJC44FJNAj1O3DB4igh98Eb0txd4zrvbRmPIx5OIH8MBGTNcwFhArUE1XB8TmA7IaqIYLvc3VP7ChG5+QrWArEGTz+sSOtdU5VZbrZTpR+92+pa0HY4r", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Material No." = _t, #"Date of Purchase" = _t, #"Quantity PC" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material No.", type text}, {"Date of Purchase", type date}, {"Quantity PC", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Stock Balance", each List.Accumulate({[Index]..7},72000,(state,current)=>state-#"Added Index"{current}[Quantity PC]),Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Age in Days", each if [Stock Balance]<0 then 0 else Int64.From(#date(2023,5,11)-[Date of Purchase]),Int64.Type)
in
    #"Added Custom1"

Here is the basic process.  Still needs to be adjusted for multiple Material numbers  - your sample data was not good enough for that.