Ayuda con la diferencia de tiempo entre varias filas por día y mes

[j_martinho]

¡Hola!

Necesito calcular el saldo mensual por hora para cada "MAT".
En mi fuente de datos tengo "DATA" como fecha, "HOUR" como hora y "FL_CRT_ENTRADA_SAIDA" como indicador de entrada o salida.

Por ejemplo:

El 16/01 "MAT" 310668 entró 7:48 am, a la izquierda 12:04 pm, entró 13:04 pm de nuevo y salió 16:58 pm. Saldo desde: 08:10 h o 8,1666 horas

¿Puedes ayudarme a modelar y calcular?

[Syndicate_Admin · Accepted solution]

No estoy seguro de qué otros análisis tendrá que hacer con sus datos, pero para el que preguntó, pivotaría sus datos para que obtenga Enter y Exit en la misma fila, para que pueda usar Duration.TotalHours() para calcular la duración. Por favor, coloque este código M en una consulta en blanco para ver cómo hacerlo con sus datos. Tenga en cuenta que hice OCR para extraer sus datos de la foto, pero no dividió todas las columnas, así que tuve que hacer pasos adicionales al principio para abordar eso (que no tendrá que hacer con sus datos).

dejar
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddK7bgMhEAXQX7G2tuR5MI+lWyku3KRI0ln+/98IYMW7DjMV6CIdXQbu94WAAFCLFnFczgsjqHrboF4AL+PYavEKcNqjdt7Dtlw/f762j215nA+WElJsIVUokzXStn5vt/+UoSXUCTm0+GmFxdxVk2JaZb7kSMNiBlbkjbJXMWgQD8sOVg+TXkYOmGBtNoUmbKRxMVaHzOK/l7T3iaVPaSLCmeaVy6z1NG5mWtajRXD4YoKd2qPxxQSzXqupxVYbDa2TNdKwloOhJxSHFD+pqJcLcHLHPi+iWevpXuzxCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CHAVE = _t, MAT = _t, HORA = _t, #"4 KORA" = _t, #"4 FL_CTR_ENTRADA_SAIDA" = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Source,{{"CHAVE", Int64.Type}, {"MAT", Int64.Type}, {"HORA", type text}, {"4 KORA", type time}, {"4 FL_CTR_ENTRADA_SAIDA", type text}}),
#"Dividir columna por delimitador" = Table.SplitColumn(#"Changed Type", "HORA", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"HORA.1", "HORA.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"HORA.1", escriba text}, {"HORA.2", escriba text}}),
#"Columnas renombradas" = Table.RenameColumns(#"Changed Type1",{{"HORA.2", "Date"}, {"4 KORA", "Time"}}),
#"Índice agregado" = Table.AddIndexColumn(#"Columnas cuyo nombre ha cambiado", "Índice", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "ForPivot", cada Number.RoundUp([Index]/2), escriba text),
#"Columnas eliminadas" = Table.RemoveColumns(#"Added Custom",{"CHAVE", "HORA.1", "Index"}),
#"Columna dinámica" = Table.Pivot(#"Columnas eliminadas", List.Distinct(#"Columnas eliminadas"[#"4 FL_CTR_ENTRADA_SAIDA"]), "4 FL_CTR_ENTRADA_SAIDA", "Hora"),
#"Added Custom1" = Table.AddColumn(#"Columna dinámica", "Horas de duración", cada Duration.TotalHours([SAIDA]-[ENTRADA])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration Hours", type number}})
en
#"Tipo cambiado2"

Si esto funciona para usted, por favor márquelo como la solución. Las felicitaciones también se aprecian. Por favor, hágamelo saber si no.

saludos

palmadita

[v-alq-msft]

Hola, @j_martinho

En función de su descripción, supongo que desea calcular el valor acumulado de la diferencia de tiempo. Creé datos para reproducir su escenario. El archivo pbix se adjunta al final.

Mesa:

Puede crear dos columnas calculadas y una medida como se muestra a continuación.

Calculated column:
DATA = DATE(YEAR('Table'[DATA-HORA]),MONTH('Table'[DATA-HORA]),DAY('Table'[DATA-HORA]))
HORA = TIME(HOUR('Table'[DATA-HORA]),MINUTE('Table'[DATA-HORA]),SECOND('Table'[DATA-HORA]))

Measure:
Balance = 
var tab =
ADDCOLUMNS(
    ALL('Table'),
    "timediff",
    var flag = 'Table'[FL_CTR_ENTRADA_SAIDA]
    var mat = 'Table'[MAT]
    var _datetime = 'Table'[DATA-HORA]
    return
    IF(
        flag = "SAIDA",
        DATEDIFF(
            CALCULATE(
                MAX('Table'[DATA-HORA]),
                FILTER(
                    ALL('Table'),
                    'Table'[MAT] = mat&&
                    'Table'[FL_CTR_ENTRADA_SAIDA] = "ENTRADA"&&
                    'Table'[DATA-HORA]<_datetime
                )
            ),
            _datetime,MINUTE
        )
    )
)
var _mat = SELECTEDVALUE('Table'[MAT])
var _datetime = SELECTEDVALUE('Table'[DATA-HORA])

var result = 
    SUMX(
        FILTER(
            tab,
            [MAT] = _mat&&
            [DATA-HORA]<=_datetime
        ),
        [timediff]
    )
return
IF(
    ISBLANK(result),
    "0h:0m",
    INT(result/60)&"h:"&MOD(result,60)&"m"
)    

Resultado:

Saludos

Allan

Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[Syndicate_Admin]

Hola, @v-alq-msft

Gracias. Funcionó para muestras pequeñas. En mi base tengo alrededor de 5 millones de registros y las imágenes se quedaron atascadas.

Traté de poner el resultado en un gráfico de barras con el eje Año / Mes, pero tampoco funcionó.

Como resultado, tengo que mostrar en una tabla o gráfico el total de horas por mes y el mat total por mes.

Estoy tratando de ajustar la solución enviada, pero todavía aprendiendo.