Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jt1999
Frequent Visitor

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!

2 ACCEPTED SOLUTIONS
vapid128
Solution Specialist
Solution Specialist

image.png

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])
    )
)

View solution in original post

v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1658461319394.png

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:

vyalanwumsft_1-1658461361107.png


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.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1658461319394.png

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:

vyalanwumsft_1-1658461361107.png


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.

vapid128
Solution Specialist
Solution Specialist

image.png

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])
    )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors