Machine Capacity Planning

[TimmK]

I have four machines, each with a capacity of 960 minutes. At any given time, only one job can be processed in a machine. The jobs often have varying processing times in the machine.

The table "SL" provides all the necessary information. It has columns "Date", "Order Key", and "Duration". "Date" is the binding delivery date, "Order Key" is the order number, and "Duration" is the processing time required for each order.

I now want a Power BI DAX Measure that calculates the ideal machine allocation for me, so that as many orders as possible are processed by the deadline (i.e., by the delivery date).

Is this possible within Power BI? If yes, how?

[Anonymous · Accepted solution]

Hi @TimmK ,

I suggest you to sort your order by Date and Duration. We will prioritize completing the orders which are near the deadline or the shorter duration ones.

Sort Order by Date and Duration = 
VAR _ADD1 =
    ADDCOLUMNS (
        FILTER ( 'Table', 'Table'[Duration] <= 960 ),
        "Rank",
            VAR _RANK1 =
                RANKX ( 'Table', 'Table'[Duration],, ASC, DENSE )
            VAR _RANK2 =
                RANKX ( 'Table', 'Table'[Date],, ASC, DENSE )
            RETURN
                _RANK2 * 100 + _RANK1
    )
VAR _ADD2 =
    ADDCOLUMNS ( _ADD1, "NewRank", RANKX ( _ADD1, [Rank],, ASC, DENSE ) )
RETURN
    SUMX (
        FILTER ( _ADD2, [Order Key] = EARLIER ( 'Table'[Order Key] ) ),
        [NewRank]
    )

Result is as below. Blank one means that duration is out of machine support.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.