Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone,
I am attempting to create a running evolution of inventory based on demand. The problem is essentially this: I want to create a running log of inventory based on consumption of product as time goes on. This is to answer the questiion, in a worst case scenario, and we do not receive the material we need, when will we run out of product. 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.
If that was not clear, please refer to the chart I copied and pasted below. 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 |
The inventory only appears in the month of July because in PowerQuery I merged the inventory to appear only in the same month as the current demand month, which in this case is July 2022.
Any advice would be a great help, thank you!
Solved! Go to Solution.
Running Inventory =
CALCULATE(
MAX('Table'[Current Inventory]),
ALLEXCEPT('Table','Table'[Part Number])
)
-
CALCULATE(
SUM('Table'[Demand]),
FILTER(
'Table',
'Table'[Date]<=EARLIER('Table'[Date]) &&
'Table'[Part Number] = EARLIER('Table'[Part Number])
)
)
Hi, @jt1999 ;
Create a measure by dax.
Runing Inventory = CALCULATE(SUM([Current Inventory])-SUM([Demand]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date])&&[Part Number]=MAX('Table'[Part Number])))
the final show:
Or create a column by dax.
Running Inventory = CALCULATE(SUM([Current Inventory])-SUM([Demand]),FILTER('Table',[Part Number]=EARLIER('Table'[Part Number])&&[Date]<=EARLIER('Table'[Date])))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @jt1999 ;
Create a measure by dax.
Runing Inventory = CALCULATE(SUM([Current Inventory])-SUM([Demand]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date])&&[Part Number]=MAX('Table'[Part Number])))
the final show:
Or create a column by dax.
Running Inventory = CALCULATE(SUM([Current Inventory])-SUM([Demand]),FILTER('Table',[Part Number]=EARLIER('Table'[Part Number])&&[Date]<=EARLIER('Table'[Date])))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Running Inventory =
CALCULATE(
MAX('Table'[Current Inventory]),
ALLEXCEPT('Table','Table'[Part Number])
)
-
CALCULATE(
SUM('Table'[Demand]),
FILTER(
'Table',
'Table'[Date]<=EARLIER('Table'[Date]) &&
'Table'[Part Number] = EARLIER('Table'[Part Number])
)
)
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |