topic Re: Time Evolution of Inventory Using DAX in DAX Commands and Tips

Time Evolution of Inventory Using DAX

jt1999 — Tue, 19 Jul 2022 19:12:27 GMT

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!

Re: Time Evolution of Inventory Using DAX

johnt75 — Wed, 20 Jul 2022 09:08:44 GMT

Running Total = var maxDate = MAX('Date'[Date]) return CALCULATE( SUMX('Table', 'Table'[Current inventory] - 'Table'[Demand]), 'Table'[Date] <= maxDate )

Re: Time Evolution of Inventory Using DAX

Anonymous — Wed, 27 Jul 2022 08:33:19 GMT

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.