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

 

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. 

 

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

 

 

Any advice would be a great help, thank you!

1 ACCEPTED SOLUTION
v-yetao1-msft
Community Support
Community Support

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 .

Ailsamsft_0-1658910746432.png

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.

View solution in original post

2 REPLIES 2
v-yetao1-msft
Community Support
Community Support

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 .

Ailsamsft_0-1658910746432.png

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.

johnt75
Super User
Super User

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

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