Forecasting supply and demand

[DennisV]

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

[v-piga-msft]

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

[DennisV]

@v-piga-msft

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

[DennisV]

No one with a solution for this problem?