How to reschedule EDD based on production capacity - Power Query

[shafiz_p]

[Power Query] Issue with cumulative sum and capacity logic

Update: This post was originally published in the DAX forum by mistake. Based on replies and the nature of the issue, it should be addressed using Power Query. I’ve posted it in the power query forum. Thanks for your understanding

Here is the link

I have a product wise capacity table. See below image:

I have a OrderDetails table with many column like Customer, Date, Order Code, CS Officer, Product Type, CS Approved Date(datetime) Order Qty(integer), Balance Qty(Integer), Estimated Delivery Date (datetime), Delivery Status, EDD (Duplicate of Estimated Delivery Date)

Problem is Each EDD of Each Product Type have multiple order entry of many different customer. Each order gets CS approved Date in a timely manner. Once order gets CS Approved Date, the order is in the queue of production and gets a Estimated Delivery Date. Within that date goods/orders needs to deliver. I wants to see which order I need to deliver tomorrow or day after tomorrow and sent those order for processing. Nicely manage the production.

But the problem is daily capacity of a perticular product type. So, I wants to see only order or number of orders, which satisfy the capacity i.e Sum of orders quantity <= Capacity of the product type. EX: Order 1 Qty + Order 2 Qty .... + N <= Product Capacity.

All the other orders of that particular day of that perticular product type gets the next EDD. 

For Better Understanding, See the example below:

So, when I filter a perticular Date (EDD) and a Perticular Product Type, I only able to see orders sums up less than capacity of that product and remaining order will move to next EDD of the same product type and this process may push the listed EDD further. Additionally , All Listed EDD are Working Days (No weekends and holidays listed in this column). So, after pusing next day, if any, it must need to be woking days.

For example, OrderDetails have 2 EDD consecutively, 9/26/2025 and 9/28/2025. There is a Gaps in the middle, which is 9/27/2025. After pusing order of 26th (If any, which are not fitting within the capacity) to next day 27th , it must need to check for working days, if working days, then create the assignEDD, if not, move on to next day, which is 9/28/2025 (Definitly it is working days because this date present in the EDD). So, any new date which is not present in the EDD, must need to check in the Calendar Table for working days.

Calendar Table: If, Is Working Day = True -> It is Working Day otherwise not.

Complete Logic:

Consider the Product type is a Big Bucket -> Each Product Type EDD's (Consider Small Bucket) -> Sort it oldest to newest -> Inside bucket sort orders by CS Approved Date (FIFO i.e First in first out) -> Each day, pick orders whose total quantity fits within the product type's capacity -> Send remaining orders beyond capacity to the next working day -> Rerun the logic for capacity testing (Previous day orders + Current Day Orders) -> Lastly, If any perticular order is greater than capacity, don't need to change EDD, just keep it as it is as well as for Urgent in the "CS Coment" Column 

Create New column with your calculation and Named it AssignedEDD and Flag (If > Capacity and Urgent)

Note: OrderDetails table will grow day by day

Find the attached power bi file and open power query to solve the problem.

Thanks!!
Shahariar Hafiz

[shafiz_p]

Dynamic EDD Reassignment Based on Capacity and CS Approval 

I have a product wise capacity table. See below image:

I have a OrderDetails table with many column like Customer, Date, Order Code, CS Officer, Product Type, CS Approved Date(datetime) Order Qty(integer), Balance Qty(Integer), Estimated Delivery Date (datetime), Delivery Status, EDD (Duplicate of Estimated Delivery Date)

Problem is Each EDD of Each Product Type have multiple order entry of many different customer. Each order gets CS approved Date in a timely manner. Once order gets CS Approved Date, the order is in the queue of production and gets a Estimated Delivery Date. Within that date goods/orders needs to deliver. I wants to see which order I need to deliver tomorrow or day after tomorrow and sent those order for processing. Nicely manage the production.

But the problem is daily capacity of a perticular product type. He only can process a certain amount per day. So, he wants to see only order or number of orders, which satisfy the capacity i.e Sum of orders quantity <= Capacity of the product type.

All the other orders of that particular day of that perticular product type gets the next EDD. 

For Better Understanding, See the example below:

So, when I filter a perticular Date (EDD) and a Perticular Product Type, I only able to see orders sums up less than capacity of that product and remaining order will move to next EDD and this process may push the listed EDD further. Additionally , All Listed EDD is calculated Working Days. So, after pusing next day, if any, it must need to be woking days.

For example, OrderDetails have 2 EDD consecutively, 9/26/2025 and 9/28/2025. Gaps in the middle is not working days. after pusing order of 26th to next day 27th , it is invalid (Need to push 28th). if reachs the bounday of EDD meaning Last EDD and pushed to next day, then this date goes to calendar table and check the available working days.

Find the attached complete file to work with.

Thanks,
Shahariar Hafiz

[lbendlin]

1. Power BI is a reporting tool, not a resource management tool

2. Conditional aggregations can only be done in Power Query, not in DAX.

[shafiz_p]

As far as I recall, I posted this problem in the Power Query forum. However, I noticed it's showing up in the DAX forum—perhaps I overlooked the title or label.

I'm reposting this in the Power Query forum.

Thanks.