The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to Solution.
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.
Thanks so much! I will have a look and get back to you! Cheers, Bryant
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.
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 |
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.
Yes, 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
Please read the second sentence of my previous message.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |