Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I have a data model that contains the below tables.
Inventory - snapshot of current inventory position as of today
PO Table - Open PO's awaiting delivery
Open Deliveries - Open deliveries waiting to be picked and shipped
Open Orders - Open sales orders that will be picked and shipped in the future
These tables are all linked to the inventory table and a date table. What I am trying to do is take the current inventory postion for todays date (shows in week 40 below) and add the PO amount to that qty and subtract the deliveries and sales order quatities. The Inventory Rolling measure does just that. However I need to then utilize the result from today as the Inventory position for tomorrow and so on down the line.
Week 40 shows inventory ending at -305,468. I need that to be the starting inventory position for week 41 and then I would use that figure and add in week 41 PO qtys and subtract week 41 deliveries and order qty's. Any ideas how I can best utilize the previous days inventory record for the current inventory level?
Thanks,
Mike
Solved! Go to Solution.
Hi, @9mikejacobs
Here are the steps you can follow to achieve
3.create measure for Inventory Rolling
Inventory Rolling = var _index=MAX('Table'[Index])
var _inv=CALCULATE(sum('Table'[ Inventory ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
var _poq=CALCULATE(sum('Table'[ PO Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
var _ooq=CALCULATE(sum('Table'[ Open Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
var _odq=CALCULATE(sum('Table'[ Open Deliveries Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
return _inv+_poq-_ooq-_odq
4.create measure for inventory:
Inventory = var _inv=CALCULATE([Inventory Rolling],FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))
return if(ISBLANK(_inv),CALCULATE(MAX('Table'[ Inventory ]),'Table'[Index]=1),_inv)
And you can get your expected chart like this:
Best Regards,
Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @9mikejacobs
Here are the steps you can follow to achieve
3.create measure for Inventory Rolling
Inventory Rolling = var _index=MAX('Table'[Index])
var _inv=CALCULATE(sum('Table'[ Inventory ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
var _poq=CALCULATE(sum('Table'[ PO Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
var _ooq=CALCULATE(sum('Table'[ Open Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
var _odq=CALCULATE(sum('Table'[ Open Deliveries Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))
return _inv+_poq-_ooq-_odq
4.create measure for inventory:
Inventory = var _inv=CALCULATE([Inventory Rolling],FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))
return if(ISBLANK(_inv),CALCULATE(MAX('Table'[ Inventory ]),'Table'[Index]=1),_inv)
And you can get your expected chart like this:
Best Regards,
Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@9mikejacobs Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Row one computes correctly as my raw data has the inventory value of 126,600. So at end of week 40 I'm left with inventory of -254,200 which you see in the "inventory rolling" column. Now I need that value to drop in the inventory value for row 2 and then the computation occurs on that row and inventory at end of week 41 is -114,200. I need that to continue down the table.
My dilemma is how can I store/retrive that result and use it as my inventory value for the next record.
row 1 - 126,000 + PO Order Qty - Open Deliveries Qty - Open Order Qty
row 2 - -254,200 + PO Order Qty - Open Deliveries Qty - Open Order Qty
Hopefully that makes more sense!
What I want to happen:
YearWeek | Material Plant Key | Inventory | PO Order Qty | Open Deliveries Qty | Open Order Qty | Inventory Rolling | My Inventory value at end of week |
2020-40 | 2023-4102 | 126,600 | 380,800 | (254,200) | |||
2020-41 | 2023-4102 | (254,200) | 140,000 | (114,200) | |||
2020-42 | 2023-4102 | (114,200) | 168,000 | 53,800 | |||
2020-43 | 2023-4102 | 53,800 | 120,000 | 4,500 | 169,300 | ||
2020-44 | 2023-4102 | 169,300 | 220,000 | 389,300 | |||
2020-45 | 2023-4102 | 389,300 | - | 389,300 | |||
2020-46 | 2023-4102 | 389,300 | 90,000 | 299,300 | |||
2020-47 | 2023-4102 | 299,300 | 156,000 | 156,000 | 455,300 | ||
2020-48 | 2023-4102 | 455,300 | 264,000 | 264,000 | 719,300 | ||
2020-49 | 2023-4102 | 719,300 | 80,000 | (80,000) | 639,300 | ||
2020-50 | 2023-4102 | 639,300 | 120,000 | 120,000 | 759,300 | ||
2020-51 | 2023-4102 | 759,300 | 300,000 | 600,000 | (300,000) | 459,300 | |
2020-52 | 2023-4102 | 459,300 | - | 459,300 | |||
2021-01 | 2023-4102 | 459,300 | - | 459,300 | |||
2021-02 | 2023-4102 | 459,300 | 372,000 | 850,000 | (478,000) | (18,700) | |
2021-03 | 2023-4102 | (18,700) | - | (18,700) | |||
2021-04 | 2023-4102 | (18,700) | 144,000 | 144,000 | 125,300 |
What is happening:
YearWeek | Material Plant Key | Inventory | PO Order Qty | Open Deliveries Qty | Open Order Qty | Inventory Rolling | |
2020-40 | 2023-4102 | 126,600 | 380,800 | (254,200) | |||
2020-41 | 2023-4102 | 140,000 | 140,000 | ||||
2020-42 | 2023-4102 | 168,000 | 168,000 | ||||
2020-43 | 2023-4102 | 120,000 | 4,500 | 115,500 | |||
2020-44 | 2023-4102 | 220,000 | 220,000 | ||||
2020-45 | 2023-4102 | - | |||||
2020-46 | 2023-4102 | 90,000 | (90,000) | ||||
2020-47 | 2023-4102 | 156,000 | 156,000 | ||||
2020-48 | 2023-4102 | 264,000 | 264,000 | ||||
2020-49 | 2023-4102 | 80,000 | (80,000) | ||||
2020-50 | 2023-4102 | 120,000 | 120,000 | ||||
2020-51 | 2023-4102 | 300,000 | 600,000 | (300,000) | |||
2020-52 | 2023-4102 | - | |||||
2021-01 | 2023-4102 | - | |||||
2021-02 | 2023-4102 | 372,000 | 850,000 | (478,000) | |||
2021-03 | 2023-4102 | - | |||||
2021-04 | 2023-4102 | 144,000 | 144,000 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |