cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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!

1 ACCEPTED SOLUTION
Community Support

Hi @jt1999

``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 2
Community Support

Hi @jt1999

``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.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.