Inventory DAX First in First out

[PowerBITestingG]

So I basically want to compute a formula that calculates the Current Quantity -   Delivered quantity of the MAX(Delivered date) and then do it again for the next one until the Current Quantity gets depleted like in the table below

Item ID	Current Quantity	Delivered Date	Delivered Quantity	Formula
105	45	16/08/2022	5	0
105	45	24/08/2022	23	0
105	45	01/09/2022	61	0
105	45	11/10/2022	21	21-23=-2
105	45	24/10/2022	24	45-24=21

Any ideas? thank you!

[Bifinity_75 · Accepted solution]

Hi @PowerBITestingG , try this calculate column: Name of column:"Table_"

Formula = var cumulative=CALCULATE(SUM(Table_[Delivered Quantity]),
FILTER(ALLEXCEPT(Table_,Table_[Item ID]),
Table_[Delivered Date] >= EARLIER ( Table_[Delivered Date] )))
return
if(Table_[Current Quantity]-cumulative<0,0,
Table_[Current Quantity]-cumulative
)

Best regards