Forum Discussion
Forecasting supply and demand
Hello,
I'm pretty new to Power BI and i'm trying to create a forecasting tool, that calculates when i have shortages of items.
I'm using 2 different files for this:
1. What's in my warehouse
| NAME | VERSION | AVAILABLE | AMOUNT | WEEK OF DELIVERY | DELIVERY AMOUNT |
| AER | 1 | Warehouse | 25 | ||
| STR | 1 | Warehouse | 60 | ||
| KLB | 1 | Warehouse | 55 | ||
| OPE | 1 | Warehouse | 10 | ||
| LZE | 1 | Warehouse | 40 | ||
| STR | 2 | Warehouse | 5 | ||
| KLB | 2 | Supplier | 0 | 10/2019 | 100 |
| OPE | 2 | Supplier | 0 | 6/2019 | 100 |
| AER | 2 | Supplier | 0 | 25/2019 | 100 |
2. What's the forecast
| NAME | VERSION | PERIOD | FORECAST |
| AER | 1 | 1 | 25 |
| STR | 2 | 1 | 100 |
| KLB | 1 | 2 | 28 |
| OPE | 2 | 5 | 65 |
| LZE | 1 | 5 | 80 |
| AER | 1 | 3 | 23 |
| STR | 1 | 2 | 120 |
| KLB | 2 | 3 | 56 |
| OPE | 2 | 4 | 10 |
| LZE | 2 | 4 | 25 |
| AER | 1 | 2 | 36 |
| STR | 1 | 5 | 85 |
| KLB | 1 | 6 | 46 |
| OPE | 1 | 11 | 31 |
| LZE | 2 | 1 | 21 |
Now I'm trying to see in a table when there will be issues with supply and demand that looks a little like this:
As you can see i will have an issue with product AER in period 3 but i get a delivery in week 25 (period 6) and then there will be enough stock again.
Product STR will be out of stock in period 5 so i will have to account for that and order some more by then.
I haven't found a solution about how to do this yet, if anyone could help that would be very much appreciated.
(sorry for the english mistakes, it's not my main language)
Thanks Dennis
NAME | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | <- PERIOD | |
| AER | 0 | 0 | -23 | -59 | -59 | 41 | 41 | 41 | 41 | 41 | 41 | 41 | ||
| STR | 65 | 55 | 35 | 35 | -50 | -50 | -50 | -50 | -50 | -50 | -50 | -50 | ||
| KLB | 55 | 27 | 127 | 71 | 71 | 25 | 25 | 25 | 25 | 25 | 25 | 25 | ||
| OPE | 10 | 110 | 110 | 110 | 45 | 45 | 45 | 45 | 45 | 45 | 14 | 14 | ||
| LZE | 19 | 19 | 19 | 19 | -61 | -61 | -61 | -61 | -61 | -61 | -61 | -61 |
|
3 Replies
v-piga-msftResident Rockstar
Hi DennisV,
I'm still a little confused about your scenario.
Is the first two table your data sample, and the last one is your desired output?
If it is, could you explain your logic in more details.
As you can see i will have an issue with product AER in period 3 but i get a delivery in week 25 (period 6) and then there will be enough stock again.
Product STR will be out of stock in period 5 so i will have to account for that and order some more by then.
If not, please share your data sample and your desired output so that we could help further on it.
Best Regards,
Cherry
Indeed the last table is the desired output.
I will again take AER as example:
- The first table is the actual stock
AER 1 Warehouse 25 AER 2 Supplier 0 25/2019 100 So I have 25 pieces in stock and ther will be 100 pieces delivered in week 25 (Period 6)
- The second table is te forecast by period
NAME VERSION PERIOD FORECAST AER 1 1 25 AER 1 3 23 AER 1 4 36 - The last table is the desired output
NAME
1 2 3 4 5 6 7 8 9 10 11 12 <- PERIOD AER 0 0 -23 -59 -59 41 41 41 41 41 41 41 I have enough stock for period 1 and 2 (the 25 pieces in the warehouse) but I will not have enough in period 3. According to the forecast i will need another 23 pieces in period 3 but I have none left, in period 4 I will need another 36 pieces so the stock will be at -59. In period 6 i get 100 pieces delivered so then it will be back at +41.
Conclusion: I will need to order some more parts by period 3 or I will not be able to supply to my customers.
I hope this makes more sense now, if not don't hesitate to contact me.
Dennis
No one with a solution for this problem?
