Forum Discussion
Time Evolution of Inventory Using DAX
I am attempting to create a running evolution of inventory based on demand. The problem is essentially this: I need to create a running log of inventory based on consumption of product as time goes on. . I believe this is easier to accomplish in DAX, but if anyone has a solution in PowerQuery as well, I would be open to hear it.
Please refer to the chart I copied and pasted below. Running Inventory is the column I am trying to create. Please make note of the two different part numbers.
| Date | Part Number | Demand | Current Inventory (current month) | Running Inventory |
| 7/1/2022 | 123 | 500 | 4700 | 4200 |
| 7/1/2022 | 345 | 500 | 1000 | 500 |
| 8/1/2022 | 123 | 600 | 3600 | |
| 9/1/2022 | 123 | 550 | 3050 | |
| 9/1/2022 | 345 | 500 | 0 | |
| 10/1/2022 | 123 | 440 | 2610 | |
| 11/1/2022 | 123 | 700 | 1910 | |
| 12/1/2022 | 123 | 550 | 1360 | |
| 1/1/2023 | 123 | 575 | 785 | |
| 2/1/2023 | 123 | 600 | 185 |
Any advice would be a great help, thank you!
Hi jt1999
Please try this measure .
Measure = CALCULATE(SUM('Table'[Current Inventory(current month)])- SUM('Table'[Demand]), FILTER(ALL('Table'),[Part Number]=MAX('Table'[Part Number]) && [Date]<=MAX('Table'[Date])))The result is as shown below .
I have attached my pbix file , you can refer to it .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Running Total = var maxDate = MAX('Date'[Date]) return CALCULATE( SUMX('Table', 'Table'[Current inventory] - 'Table'[Demand]), 'Table'[Date] <= maxDate )Hi jt1999
Please try this measure .
Measure = CALCULATE(SUM('Table'[Current Inventory(current month)])- SUM('Table'[Demand]), FILTER(ALL('Table'),[Part Number]=MAX('Table'[Part Number]) && [Date]<=MAX('Table'[Date])))The result is as shown below .
I have attached my pbix file , you can refer to it .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
