How to reschedule EDD based on production capacity - Power Query

Yes, we’re starting from scratch. For each product, the timeline begins from the first (oldest) date found in the dataset.

For you better understanding:

As I said, each product type is a big bucket. So, grouping it first. Then hop into each bucket at a time. Let's say, I hop into "Printed Fabric Label" product. In the capacity table, daily capacity for that product type is 700,000.

First day (Oldest EDD), Let's say it is 6/1/2024 (Starting point), total have 20 orders and sum of quantity is 12,00,000, which is greater than daily capacity 700,000.

What would I do?? I would not be able to deliver all the good on that day (6/1/2024) because my capacity constraint. I only be able to produce 700,000 a day for this product type.

So, I would take only orders sum ups less than or equal to 700,000 and rest of the orders would go to next EDD. Now question is how I decide, which orders i will keep and which I will sent to next EDD. Solution is First in First Out (Sort the orders of that EDD 6/1/2024 oldest to newest). Then check running sum who is fitting within the capacity.

Let's say only 15 orders fitting within the capacity, whose total sum of order quantity is less than or equal to 700,000. You found your orders to produce that day, 6/1/2024. So, create a column and name it AssignedEDD and assign the same EDD value, in the newly created column.

At the same time check, any single order value is greater than capacity 700,000. If found, create a new column and name it Flag and assign value "> than capacity" as well as keep the AssignedEDD value as EDD value.

Do the same for ["Urgent", "Urgent (Revised)", "Urgent (Additional)"] and assign value "Urgent" in the Flag column and AssignedEDD value is the value same as EDD.

At last you have 5 orders in hand, amounting to 500,000, which are not fitting within the capacity 700,000. So, we spill them to the next day, which is 6/2/2024 but unfortunately the date is not present in the EDD column. So, I need to know, is the new date is working day?? Go to Calendar table , check for true in the "Is Working Day" column for that date. If found working day, then AssignedEDD value would be that date (6/2/2024) and quantity (500,000) already satisfying the capacity. 

Move on to the next EDD in the list.

But if 6/2/2024 is not working day, obviously you move on to the next days, which is 6/3/2024 and check if this date is already in the EDD list, if, then spill all 5 orders to that date (6/3/2024).

Let's say on that date (6/3/2025), you have already 12 orders, amounting to 300,000 in hand. So, total order number on that date (6/3/2024) will be (5 orders + 12 orders) = 17 orders and total quantity is (500,000 + 300,000) = 800,000 but your capacity is 700,000. Keep 700,000 for that date (6/3/2024) and move all the rest orders to next date (6/4/2024)

continue the same process for all EDDs of All Product Type.

Ignore Blank EDD (Those are not yet approved for production). When get CS approved date, will get EDD. Definitely "CS approved date" would be current date not past date. Every day "Customer Service" team analyse order's other feature and provide approved date. Then there is lead time for each product and EDD calculated accordingly. So, EDD = CS Approved Date + Lead Time

One thing keep in mind that the file will grow day by day.