Struggling with an inventory balance based on starting point, usage, and new purchases.

[charlessutton]

I am trying to find a daily inventory balance for raw materials so I can flag any work orders that may not have enough raw material to coplete the job.

I have 3 sets of data to start with. Table 1 has Stock Code and QOH (quantity on hand), Table 2 shows a job number and the required quantity for each stock code based on job date, and Table 3 shows purchase order, scheduled receipt date, and inbound quantity for each stock code. 

I am trying to find a running balance into the future to see when we may have a short fall. I am struggling using the QOH as the starting balance and then adding or reducing by day depending on the work order or inbound purchase. I have tried multiple things but I cannot seem to get a good starting point. Any guidance or new path is appreciated. 

Tabe 1

stock	qoh
10001	17739
10002	591
10003	4710
10004	0
10005	0
10006	0
10007	1198
10008	344
10009	0
10010	0

Table 2

Job	StockCode	WipMaster.JobStartDate	WipMaster.Complete	RMNeeded
21779	70003	44120	N	0
21779	70003	44120	N	2255.027
22006	61003	44139	N	15.3
22006	61005	44139	N	15.3
22006	61002	44139	N	15.3
22006	61001	44139	N	15.3
22006	50030	44139	N	41496
21938	20029	44145	N	59746.5
21938	40027	44145	N	60918
21938	60008	44145	N	60918
21938	10036	44145	N	9957.926
21938	30095	44145	N	60332.25
21938	30096	44145	N	60332.25
21938	62055	44145	N	9811.488

Table 3

PurchaseOrder	StockCode	MOrderQty	LatestDueDate
9591	50069	18000	12/31/2020 0:00
9591	50069	18000	12/31/2020 0:00
9541	50069	18000	12/31/2020 0:00
9763	20025	50000	12/31/2020 0:00
9685	20029	60000	12/31/2020 0:00
9789	SUBCON- 90074	25000	12/31/2020 0:00
9302	40018	30000	12/30/2020 0:00
8828	208819	12	12/23/2020 0:00
8828	212878	30	12/23/2020 0:00
8828	574635	40	12/23/2020 0:00
8828	108311	1	12/23/2020 0:00
8828	740016	2	12/23/2020 0:00
8828	348037	2	12/23/2020 0:00
9756	90131	264	12/23/2020 0:00
9362	90131	264	12/23/2020 0:00

[V-pazhen-msft]

@charlessutton 

I am afraid it is not possible to do so. The problem is you don't have related fields in the 3 tables. All three tables record unrelated content, so no relationships can be created. For example, there is no calendar table and date column in the Table 1 (with QHO column), you cannot set QHO as starting balance because you do not know which days it start from. 

Check this doc about relationships in PBI: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.