Forum Discussion
Helper IMaterial Requirements Planning Bucket By Date/Week
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 |
Hi,
Please download the PBI file. In the Calendar Table, write a calculated column formula to calculate week number and drag that week number column to the visual.
Hope this helps.
6 Replies
- Ashish_Mathur
Super User
Hi,
For B123 and B456, shouldn't the answer be 5 for week 2? If my understanding is correct, then share a Calendar table with a week number column.
heiligbdYes, apologies, I may have made a mistake in hast of trying to get this posted. However, I would like to have this in table format instead of matrix because of other things that need to added/addressed. Therefore, I am assuming that I am going to need a variable written of some sort for this MRP like function. Any help with that would be much appreciated!
Thanks, Bryant
- Ashish_Mathur
Please read the second sentence of my previous message.
- heiligbd
Apologies, misunderstood. See below Date Table example.
Date Week # 8/1/23 1 8/8/23 2 8/16/23 3 8/24/23 4 - Ashish_Mathur
Hi,
Please download the PBI file. In the Calendar Table, write a calculated column formula to calculate week number and drag that week number column to the visual.
Hope this helps.
- heiligbd
Thanks so much! I will have a look and get back to you! Cheers, Bryant