Forum Discussion

robandemmy's avatar
robandemmy
Frequent Visitor
3 years ago
Solved

Calculating Machine Production

I have the below table:

 

MACHINELOADTIMEUNLOADTIMEProcess Time (Unload-Load)
M012022/08/04 6:32:002022/08/04 14:42:00490
M072022/08/04 6:15:002022/08/04 13:57:00462
M032022/08/04 3:24:002022/08/04 11:35:00491
M062022/08/04 2:02:002022/08/04 9:45:00463
M022022/08/04 1:02:002022/08/04 9:28:00506
M052022/08/04 0:20:002022/08/04 7:58:00458
M042022/08/03 21:54:002022/08/04 6:13:00499
M012022/08/03 20:51:002022/08/04 5:18:00507
M082022/08/03 20:33:002022/08/04 4:15:00462
M072022/08/03 19:20:002022/08/04 3:25:00485
M062022/08/03 15:35:002022/08/03 23:22:00467
M052022/08/03 15:04:002022/08/03 22:46:00462
M032022/08/03 14:31:002022/08/03 22:36:00485
M082022/08/03 12:07:002022/08/03 19:51:00464
M072022/08/03 10:58:002022/08/03 18:41:00463
M042022/08/03 10:02:002022/08/03 18:15:00493
M022022/08/03 7:44:002022/08/03 15:58:00494
M052022/08/03 5:59:002022/08/03 13:41:00462
M062022/08/03 5:24:002022/08/03 13:09:00465
M032022/08/03 4:27:002022/08/03 12:34:00487
M012022/08/03 2:54:002022/08/03 11:10:00496
M082022/08/03 1:06:002022/08/03 8:50:00464

 

Process = UNLOAD-LOAD*1440

 

We are a 24/7 operation...M shift = 7am-7pm and E shift =7pm-7am. How can I assign the correct number of minutes to each shift? Specifically, production that crosses midnight has me baffled.

 

Any help is greatly appreciated!

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi robandemmy ,

     

    I forgot to modifiy something in the E shift calculation, it has been changed now. Please try:

    M shift = 
VAR _a =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _b =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 19, 0, 0 )
VAR _c =
    DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _sameday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _a
            && [UNLOADTIME] < _b, DATEDIFF ( _a, [UNLOADTIME], MINUTE ),
        [LOADTIME] < _a
            && [UNLOADTIME] >= _b, DATEDIFF ( _a, _b, MINUTE ),
        [LOADTIME] >= _a
            && [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _a
            && [LOADTIME] <= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _b, MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _b, 0
    )
VAR _diffday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _b
            && [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], _b, MINUTE ),
        [LOADTIME] < _b
            && [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _b, MINUTE ) + DATEDIFF ( _c, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] < _c, 0,
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _c, DATEDIFF ( _c, [UNLOADTIME], MINUTE )
    )
RETURN
    IF ( _a = _c, _sameday, _diffday )

E shift = 
VAR _a =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _b =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 19, 0, 0 )
VAR _c =
    DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _sameday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _a
            && [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], _a, MINUTE ),
        [LOADTIME] < _a
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _a, MINUTE ) + DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _a
            && [UNLOADTIME] < _b, 0,
        [LOADTIME] >= _a
            && [LOADTIME] <= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE )
    )
VAR _diffday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _b
            && [UNLOADTIME] < _c, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] < _b
            && [UNLOADTIME] >= _c, DATEDIFF ( _b, _c, MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _c, MINUTE )
    )
RETURN
    IF ( _a = _c, _sameday, _diffday )

    Final output:

    Best Regards,

    Jianbo Li

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

     

6 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    robandemmy,

     

    In Power Query, try this expression in a custom column:

     

    Duration.TotalMinutes([UNLOADTIME] - [LOADTIME])

     

     

  • robandemmy's avatar
    robandemmy
    Frequent Visitor
    3 years ago

    Thanks for the reply...that's a partial solution. What I need to be able to do is assign the minutes to M and E shifts in a 7-7 operation...for example, if a unit loads at 5pm and unloads at 10pm, then M shift gets 120 minutes and E shift gets 180 minutes. That is one of the more simple examples...units that load at 6pm and unload at 2am for instance. M shift would get 60 minutes and E shift 420 minutes. Crossing midnight adds another level of complexity 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi robandemmy ,

     

    Please try:

     

    M shift = 
VAR _a =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _b =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 19, 0, 0 )
VAR _c =
    DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _sameday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _a
            && [UNLOADTIME] < _b, DATEDIFF ( _a, [UNLOADTIME], MINUTE ),
        [LOADTIME] < _a
            && [UNLOADTIME] >= _b, DATEDIFF ( _a, _b, MINUTE ),
        [LOADTIME] >= _a
            && [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _a
            && [LOADTIME] <= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _b, MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _b, 0
    )
VAR _diffday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _b
            && [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], _b, MINUTE ),
        [LOADTIME] < _b
            && [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _b, MINUTE ) + DATEDIFF ( _c, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] < _c, 0,
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _c, DATEDIFF ( _c, [UNLOADTIME], MINUTE )
    )
RETURN
    IF ( _a = _c, _sameday, _diffday )

E shift = 
VAR _a =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _b =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 19, 0, 0 )
VAR _c =
    DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _sameday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _a
            && [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], _a, MINUTE ),
        [LOADTIME] < _a
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _a, MINUTE ) + DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _a
            && [UNLOADTIME] < _b, 0,
        [LOADTIME] >= _a
            && [LOADTIME] <= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE )
    )
VAR _diffday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _b
            && [UNLOADTIME] < _c, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] < _b
            && [UNLOADTIME] >= _c, DATEDIFF ( _b, _c, MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _c, MINUTE )
    )
RETURN
    IF ( _a = _c, _sameday, _diffday )

     

    Final output:

    Best Regards,

    Jianbo Li

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

    • robandemmy's avatar
      robandemmy
      Frequent Visitor
      3 years ago

      Thanks so much!! It's close but I am getting a few values that aren't coming back correct...looks like units that cross midnight:

      I added a total time column as valiodation and am getting negative or incorrect large values when units cross over midnight.

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi robandemmy ,

     

    I forgot to modifiy something in the E shift calculation, it has been changed now. Please try:

    M shift = 
VAR _a =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _b =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 19, 0, 0 )
VAR _c =
    DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _sameday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _a
            && [UNLOADTIME] < _b, DATEDIFF ( _a, [UNLOADTIME], MINUTE ),
        [LOADTIME] < _a
            && [UNLOADTIME] >= _b, DATEDIFF ( _a, _b, MINUTE ),
        [LOADTIME] >= _a
            && [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _a
            && [LOADTIME] <= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _b, MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _b, 0
    )
VAR _diffday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _b
            && [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], _b, MINUTE ),
        [LOADTIME] < _b
            && [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _b, MINUTE ) + DATEDIFF ( _c, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] < _c, 0,
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _c, DATEDIFF ( _c, [UNLOADTIME], MINUTE )
    )
RETURN
    IF ( _a = _c, _sameday, _diffday )

E shift = 
VAR _a =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _b =
    DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
        + TIME ( 19, 0, 0 )
VAR _c =
    DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
        + TIME ( 7, 0, 0 )
VAR _sameday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _a
            && [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], _a, MINUTE ),
        [LOADTIME] < _a
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _a, MINUTE ) + DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _a
            && [UNLOADTIME] < _b, 0,
        [LOADTIME] >= _a
            && [LOADTIME] <= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE )
    )
VAR _diffday =
    SWITCH (
        TRUE (),
        [LOADTIME] < _b
            && [UNLOADTIME] < _c, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
        [LOADTIME] < _b
            && [UNLOADTIME] >= _c, DATEDIFF ( _b, _c, MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _c, MINUTE )
    )
RETURN
    IF ( _a = _c, _sameday, _diffday )

    Final output:

    Best Regards,

    Jianbo Li

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