Forum Discussion

TimmK's avatar
TimmK
Helper IV
3 years ago
Solved

Machine Capacity Planning

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's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.