Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |