How to reschedule EDD based on production capacity - Power Query

[shafiz_p]

[Power Query] Issue with cumulative sum and capacity logic.

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 (Group it first) -> Inside Each Product Type, Each EDD's (Consider Small Bucket, group it second) -> Sort the small bucket (EDD Column) oldest to newest -> Inside small bucket, sort orders by CS Approved Date (FIFO i.e First in first out) -> Each day (Meaning each EDD), pick orders whose total quantity fits within the product type's daily capacity (Mentioned in the Capacity Table) -> Remaining orders which are not fitting within Product Type daily capacity, send 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 or assign new 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 Create a Flag column, If you found > 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

[v-kpoloju-msft]

Hi @shafiz_p,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @AlienSx, @lbendlin, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member @AlienSx, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

if you are still facing the issue you can reach out to partner who can help you below is the link: Power BI Partners | Microsoft Power Platform

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

Hi @shafiz_p,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[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.

[AlienSx]

@shafiz_p your current daily production capacity (e.g. OFFSET = 100,000) is not enough to place all orders across your current working calendar. I also did not get your message about "urgent" stuff - what's that? 

Can't also comment on your "table will grow day by day". This code just works with the list of orders in OrderDetails 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(
            List.Difference(
                Table.SelectRows(CalendarTable, (x) => x[Is Working Day] = "True")[Date],
                Yearly_Holidays[Date]
            )
        )
    ),
    // capacity by product type
    caps = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Capacity))),
    // orders sorted by prod type and estimated DD
    sorted = Table.Sort(OrderDetails, {"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"})
in
    result

[shafiz_p]

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

PRINTED FABRIC LABEL	620000
NARROW FABRIC	44000
OFFSET	600000
LEATHER	25000
RUBBER PATCH	6000
SCREEN PRINT	180000
WOVEN LABELS	350000
HIGH DENSITY	4000
HEAT TRANSFER LABEL	120000
THERMAL	150000
ACCESSORIES	700000

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.

[AlienSx]

@shafiz_p , we finally see "CS comment" column and blank EDD in your data.

# offset is not working or weird result: even if we set capacity for OFFSET to 600 000 then we still hit the limit of working days in your calendar. Your data (the one from PBI file) has total order quantity at around 215.7m. Lets make a rough estimation and divide it by max capacity of 600K => we need at least full 360 working days since Sep 01, 2024 (earliest EDD). Your calendar has only 365 working days in your calendar to work on OFFSET orders. No wonder why we hit the limit. 

# filter out orders: you keep telling that we don't need to touch orders with blank EDD (you filter them out) as well as orders exceeding daily capacity and orders with "urgent"-like status in CS comment field (I don't see any other status but "urgent..." in that column by the way). Then why did you filter out blank EDD only? Do we need to show smth in "assigned_edd" (calculated) field for orders with excess capacity and/or CS comment <> null ? 

# columns in your real data: you don't need to select columns for calculations - it's useless and bring lots of problems. As you see, I am working with the list of lists (not list of records). So that qty and EDD fields positioning is quite important. The number of columns in not important - just positions of qty and edd fields. It's really wierd that you see only 19 columns - this is really unexpected result. Anyway, there is no problem to work with a list of records to retain field names (maybe at the cost of performance) or calculate positions of EDD and Order Qty fields before iterating the list of orders.

Please comment on the orders with comment in CS comment field (aka "urgent" orders) and "> daily capacity" orders - can we simply filter them out beforehand together with orders with blank EDD? 

[shafiz_p]

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.

[lbendlin]

Are these capacity numbers by day? by working day? What is your definition of a working day ? Every day except friday?

[shafiz_p]

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.

[lbendlin]

Do we assume there is no spillover from dates before your sample data? Starting from scratch?

[shafiz_p]

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.