Forum Discussion
Solved
Inventory DAX First in First out
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!
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
1 Reply
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