Forum Discussion
DAX for efficient inventory calculations based on FIFO principles
Based on inventory transactions, I would like to calculate the inventory balance based on FIFO medthod. I would like to calculate from which year of purchase, the balance is from. However, with bad D...
Hi Alisa,
The data looks somewhat like this:
| Transaction Date | Transaction Type | Storeroom ID | Part Number | Quantity | Unit Cost | Line Cost |
| 2015-01-01 00:00 | RECEIPT | 603 | 9637669 | 50 | 556 | 27800 |
| 2016-06-21 00:00 | RECBALADJ | 603 | 9637669 | 0 | 556 | 0 |
| 2016-08-11 00:00 | ISSUE | 603 | 9637669 | -48 | 556 | -26688 |
| 2016-08-22 00:00 | RECEIPT | 603 | 9637669 | 60 | 556 | 30024 |
| 2017-07-06 00:00 | ISSUE | 603 | 9637669 | -14 | 502,19 | -7030,66 |
| 2017-08-22 00:00 | ISSUE | 603 | 9637669 | -48 | 502,19 | -24105,12 |
| 2017-08-24 00:00 | ISSUE | 603 | 9637669 | -48 | 502,19 | -24105,12 |
| 2017-08-24 00:00 | RETURN | 603 | 9637669 | 48 | 502,19 | 24105,12 |
| 2017-09-19 00:00 | RECEIPT | 603 | 9637669 | 60 | 556 | 30024 |
| 2017-11-02 00:00 | RECBALADJ | 603 | 9637669 | 0 | 500,4 | 0 |
| 2018-10-30 00:00 | RECBALADJ | 603 | 9637669 | 0 | 500,4 | 0 |
| 2019-07-16 00:00 | ISSUE | 603 | 9637669 | -48 | 500,4 | -24019,2 |
| 2019-08-26 00:00 | RECEIPT | 603 | 9637669 | 60 | 293,99 | 17639,4 |
| 2019-12-17 00:00 | RECBALADJ | 603 | 9637669 | 0 | 328,39 | 0 |
| 2020-10-30 00:00 | RECBALADJ | 603 | 9637669 | 0 | 328,39 | 0 |
| 2021-12-13 00:00 | RECEIPT | 603 | 9637669 | 15 | 328,39 | 4925,85 |
| 2021-12-13 00:00 | ISSUE | 603 | 9637669 | -42 | 328,39 | -13792,38 |