Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Syndicate_Admin
Administrator
Administrator

Calculadora de producción de la máquina

‎09-02-2022 07:55 AM

Tengo la siguiente tabla:

MÁQUINATIEMPO DE CARGATIEMPO DE DESCARGATiempo de proceso (descarga-carga)
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

Proceso = DESCARGAR-CARGAR*1440

Somos una operación 24/7... Turno M = 7am-7pm y turno E = 7pm-7am. ¿Cómo puedo asignar el número correcto de minutos a cada turno? En concreto, la producción que cruza la medianoche me tiene desconcertado.

¡Cualquier ayuda es muy apreciada!

Message 1 of 7
227 Views
0
6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

‎09-08-2022 06:41 PM

@robandemmy ,

Olvidé modificar algo en el cálculo del turno E, ahora se ha cambiado. Por favor, pruebe:

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 )

Resultado final:

vjianbolimsft_0-1662687575535.png

Saludos

Jianbo Li

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Calculating Machine Production.pbix
Message 6 of 7
181 Views
0
Syndicate_Admin
Administrator
Administrator
In response to Syndicate_Admin

‎09-09-2022 05:22 AM

Funciona muy bien, gracias!!

Message 7 of 7
174 Views
0
Syndicate_Admin
Administrator
Administrator

‎09-08-2022 12:15 AM

@robandemmy ,

Por favor, pruebe:

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 ( _a, [UNLOADTIME], MINUTE ),
        [LOADTIME] < _b
            && [UNLOADTIME] >= _c, DATEDIFF ( _a, _b, MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
        [LOADTIME] >= _b
            && [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _b, MINUTE )
    )
RETURN
    IF ( _a = _c, _sameday, _diffday )

Resultado final:

vjianbolimsft_0-1662621223126.png

Saludos

Jianbo Li

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Calculating Machine Production.pbix
Message 4 of 7
188 Views
0
Syndicate_Admin
Administrator
Administrator
In response to Syndicate_Admin

‎09-08-2022 05:18 AM

Muchas gracias!! Está cerca, pero estoy recibiendo algunos valores que no están volviendo correctos ... parecen unidades que cruzan la medianoche:

robandemmy_0-1662639231709.png

Agregué una columna de tiempo total como valiodación y obtengo valores grandes negativos o incorrectos cuando las unidades cruzan más allá de la medianoche.

Message 5 of 7
184 Views
0
Syndicate_Admin
Administrator
Administrator

‎09-02-2022 04:46 PM

Gracias por la respuesta... esa es una solución parcial. Lo que necesito poder hacer es asignar los minutos a los turnos M y E en una operación de 7-7 ... por ejemplo, si una unidad carga a las 5 p.m. y descarga a las 10 p.m., entonces el turno M obtiene 120 minutos y el turno E obtiene 180 minutos. Ese es uno de los ejemplos más sencillos... unidades que se cargan a las 6 de la tarde y descargan a las 2 de la mañana, por ejemplo. El turno M obtendría 60 minutos y el turno E 420 minutos. Cruzar la medianoche añade otro nivel de complejidad

Message 3 of 7
193 Views
0
Syndicate_Admin
Administrator
Administrator

‎09-02-2022 03:48 PM

@robandemmy,

En Power Query, pruebe esta expresión en una columna personalizada:

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

DataInsights_0-1662158818793.png

Message 2 of 7
200 Views
0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

View All