Good day,
I am working on material requirements planning for a complex capital intensive product with significant variability in requested finished good product. I have a table of sales orders by the finished good part #, requested date and qty. I have a connection table with the finished good part # and the internal top level part #. Finally I have a multiple level Bill of Materials Required table with the internal top level part # and the required raw materials per FG part #. Examples below. I would like to create a table or matrix (see below first table example of request) in which I segment out the required raw materials by week for each of the raw materials so that I can correctly material plan. Our manufacturing process/system is limited to just several days and I want to forecast out for several months of on hand orders by weekly buckets to correctly order raw materials for manufacturing. I also have a full dates table as well to tag the week for the weekly bucketing. Not sure if I need to do this via DAX or calculated columns in a particular table either. Thanks in advance for your help as any help is much appreciated! Detail example tables below.
REQUESTED SOLUTION TABLE/MATRIX:
| Child Part# | Week 1 | Week 2 | Week 3 |
| A123 | 1 | 14 | 3 |
| A456 | 1 | 14 | 3 |
| B123 | 5 | 2 | |
| B456 | 5 | 2 | |
| C123 | 3 | 5 | |
| C456 | 3 | 5 | |
| C789 | 3 | 5 | |
SALES ORDER TABLE EXAMPLE
| Sales Order # | Line # | FG Material # | Request Date | Request Qty |
| 115522 | 1 | 2222 | 8/1/2023 | 1 |
| 115522 | 2 | 4444 | 8/1/2023 | 3 |
| 115522 | 3 | 3333 | 8/1/2023 | 5 |
| 116633 | 1 | 4444 | 8/8/2023 | 7 |
| 116633 | 2 | 2222 | 8/8/2023 | 8 |
| 117744 | 1 | 2222 | 8/15/2023 | 3 |
| 118855 | 1 | 4444 | 8/24/2023 | 4 |
| 119966 | 1 | 2222 | 8/8/2023 | 6 |
| 119966 | 2 | 3333 | 8/8/2023 | 5 |
CONNECTION HEADER TABLE
| FG Material # | Top Internal Parent Material # |
| 2222 | AAAA |
| 3333 | BBBB |
| 4444 | CCCC |
BOM REQUIREMENTS TABLE
| Top Internal Material Parent # | Child Part # | Child Qty Required |
| AAAA | A123 | 1 |
| AAAA | A456 | 1 |
| BBBB | B123 | 1 |
| BBBB | B456 | 1 |
| CCCC | C123 | 1 |
| CCCC | C456 | 1 |
| CCCC | C789 | 1 |
