How to reschedule EDD based on production capacity - Power Query

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

Yes. The 'Product Type' in the capacity table indicates, On working days, each product type is produced at its specified daily capacity.

'Working days' exclude weekends (Friday) and official yearly holidays. The calendar table attached in the Power BI file already defines working days as 'True' and non-working days as 'False'.

EDD (Estimated Delivery Date): Each day, the total order quantity in hand, exceeds daily capacity. Therefore, any orders beyond capacity must be spilled / carried over to the next working day. 

All EDDs, you found in that column are by default working days. But when orders are pushed to the next day due to capacity limits, the new date might fall on a non-working day (You find gap between EDDs or at the End of EDD Date column  for each product type) —so you need to check the calendar to confirm.

If found Blank in EDD, leave them as it is, simply ignore.

No EDD, No production.

Capacity for Offset will be 600,000. That was dummy. We can also, increase the calendar size. No problem at all. There is total 31 columns in the orginal table. and currently have 10 product types and 75k rows. daily orders will intake and size will grow day by day.

There is a column named CS Comment with a tag Urgent. I said if there is urgent, leave it as it is. Meaning don't include in the capacity logic like quantity > capacity.

I have tried your code, with the smaller version OrderDetails table I provided, working fine. But not working with the bigger version. after attaching orginal table with 31 columns, steps producing list only showing 19 columns, so unable to find index, like in orginal table index of EDD is 30 but can't find that value. Why this is happening I don't know.

I think I need to try spliting the table. One with columns you have used and other with all the remaining column with full hight which is currently 75k and will grow day by day. Once found assign EDD then merge back to the reamaining part.

Let me try and get back to you.

For offset, it is not working or weird result. All assigned EDD for that product should not exceed more than October 2025, it should arround october 15 to 20.

Please extend the duration of calendar current date + 90 days, in the calendar table M code.

Here I am attaching all the rows of the orginal table and exact product type capacity. Also, for performance I only consider the non null EDD. changed your code a little. Yearly holiday table, already merged with the calendartable. so you do not need to use yearly holiday table.

let
    // fx to find position of working day in the list
    wd_position = (d as date) => List.PositionOf(working_days, d, Occurrence.First),
    // list of working days
    working_days = List.Buffer(
        List.Sort(
                Table.SelectRows(CalendarTable, (x) => x[Is Working Day] = "True")[Date]
        )
    ),
    // capacity by product type
    caps = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Capacity))),

    // Spliting Tables
    selectedColumns = {"Date", "Order Code", "Customer", "Product Type", "CS Approved Date", "Order Qty", "Estimated Delivery Date", "EDD"},
    orderDetails_Selected = Table.SelectColumns(OrderDetails, selectedColumns),

    // Creating 2 Tables With. One with null and other with non null
    OrderDetailsWithNullEDD = Table.SelectRows(orderDetails_Selected, each [EDD] = null),

    // With non null EDD
    OrderDetailsWithNonNullEDD = Table.SelectRows(orderDetails_Selected, each [EDD] <> null),

    // Tables with the remaining columns
    remainingColumns = List.Difference(Table.ColumnNames(OrderDetails), selectedColumns),
    orderDetails_Remaining = Table.SelectColumns(OrderDetails, remainingColumns),


    // orders sorted by prod type and estimated DD
    sorted = Table.Sort(OrderDetailsWithNonNullEDD, {"Product Type", "EDD", "CS Approved Date"}),
    // and >> to list
    orders = List.Buffer(Table.ToList(sorted, (x) => x)),
    // go over the list
    processing = List.Generate(
        () => [
            i = 0,
            prod = orders{0}{3},
            iw = wd_position(orders{0}{7}),
            cap = Record.Field(caps, prod),
            flag = orders{0}{5} > cap,
            current_qty = if flag then 0 else orders{0}{5},
            cap_usage = current_qty
            //delivery_date = orders{0}{7}
        ],
        (x) => x[i] < List.Count(orders),
        (x) => [
            i = x[i] + 1,
            prod = orders{i}{3}, // current order's product type
            same_prod = x[prod] = prod, // check if prod type is the same as last order 
            cap = if same_prod then x[cap] else Record.Field(caps, prod), // current capacity
            flag = orders{i}{5} > cap, // check if qty is greater than cap
            current_qty = if flag then 0 else orders{i}{5}, // this order usage quantity
            cap_is_full = (not same_prod) or 
                (x[cap_usage] + current_qty) > cap or
                working_days{x[iw]}? is null or
                orders{i}{7} > working_days{x[iw]}, // reset capacity??
            cap_usage = if cap_is_full then current_qty else x[cap_usage] + current_qty, // capacity used
            iw = if (not same_prod) then wd_position(orders{i}{7}) // position in working days calendar
                else if cap_is_full  
                    then x[iw] + 1 
                    else x[iw]
        ],
        (x) => orders{x[i]} & (if working_days{x[iw]}? is null 
            then {null, null, null, "calendar is full"}
            else {x[cap], x[cap_usage], working_days{x[iw]}, if x[flag] then "order out of capacity" else null}
        )
    ),
    result = Table.FromList(processing, (x) => x, Table.ColumnNames(sorted) & {"capacity", "usage", "assigned_EDD", "flag"}),
    #"Changed Type" = Table.TransformColumnTypes(result,{{"Order Qty", Int64.Type}, {"assigned_EDD", type date}})
in
    #"Changed Type"

Here is the updated capacity table:

Product Type                    Capacity

for each day, offset should maintain showing orders total sum less than 600,000. other product will follow the same. Any whole order unable to fit that date, spill over to the next date, which must be working date. when spilling consider the spill orders + where you spilling, consider those order also, to check capacity logic.

Any whole order value > capacity, leave it as it is. Also, same for Urgent, Urgent (Revised), Urgent (Additional) in the cs comment column, just leave it as it is. 

Hope this info helps. Kindly check attached file here.

Hi shafiz_p,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.