Forum Discussion

jt1999's avatar
jt1999
Frequent Visitor
4 years ago
Solved

Time Evolution of Inventory Using DAX

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. 

 

DatePart NumberDemand

Current Inventory

(current month)

Running Inventory
7/1/202212350047004200
7/1/20223455001000500
8/1/2022123600 3600
9/1/2022123550 3050
9/1/2022345500 0
10/1/2022123440 2610
11/1/2022123700 1910
12/1/2022123550 1360
1/1/2023123575 785
2/1/2023123600 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!

DAX Calculation
running balance
Time Based Filter
  • vapid128's avatar
    vapid128
    4 years ago

    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])
    )
)
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

2 Replies

  • vapid128's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    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])
    )
)
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.