Buscar diferencia entre la fila actual y la anterior: problema de rendimiento

[Syndicate_Admin]

Hola

Tengo un conjunto de datos de transacciones, incluidos varios vendedores con tiempos de transacción. Necesito crear una columna calculada que devuelva la fecha y hora de la transacción anterior, lo que me permite calcular posteriormente la diferencia de fecha y hora y crear otros parámetros a su alrededor.

 

He navegado por foros y terminé con una solución fluida usando EARLIER:

Expected Result (Prev Transaction) = CALCULATE(
        MAX(Example[Transaction Time]),
        Example,
        Example[Transaction Time] < EARLIER(Example[Transaction Time]),
        Example[Salesman] = EARLIER(Example[Salesman])
        )

Sin embargo, funciona muy mal sobre mi conjunto de datos real de filas de 500k y múltiples vendedores, se quedó sin memoria después de unos minutos.

Una vez que cambié la columna de fecha y hora 'Tiempo de transacción' a la fecha, se completó rápidamente, pero como necesito calcular la diferencia de tiempo exacta entre las transacciones, dicha solución no es suficiente y estoy buscando una solución mejorada para los datos de fecha y hora .

¡Esperamos cualquier pista!

[Syndicate_Admin]

¿Alguna razón en particular por la que necesita esto como una columna calculada? Por lo general, estos cálculos se realizan con medidas.

[Syndicate_Admin]

Oh, en realidad no, la medida también estaría bien, simplemente no pude encontrar ninguna solución de medida razonable que no causara el mismo problema de rendimiento debido a que EARLIER revisó una gran tabla de registros de fecha y hora.

Soy principiante en Power BI, así que es probable que me haya perdido alguna solución de medida simple, ¿supongo?

[Syndicate_Admin]

Ok, aquí hay un par de medidas para hacer los cálculos.

Para obtener la fecha/hora de la transacción anterior por parte del vendedor:

Prev Transaction =
CALCULATE (
    MAX ( fTable[Transaction time] ),
    FILTER (
        ALLEXCEPT ( fTable, fTable[Salesman] ),
        fTable[Transaction time] < MAX ( fTable[Transaction time] )
    )
)

Si desea calcular la diferencia en horas y minutos directamente, utilice:

Diff vs Previous transaction time (HH:MM) =
VAR _Prev =
    CALCULATE (
        MAX ( fTable[Transaction time] ),
        FILTER (
            ALLEXCEPT ( fTable, fTable[Salesman] ),
            fTable[Transaction time] < MAX ( fTable[Transaction time] )
        )
    )
VAR _MinutesDiff =
    DATEDIFF ( _Prev, MAX ( fTable[Transaction time] ), MINUTE )
VAR _FinalHours =
    INT ( DIVIDE ( _MinutesDiff, 60 ) )
VAR _FinalMinutes =
    FORMAT ( MOD ( _MinutesDiff, 60 ), "00" )
RETURN
    IF ( ISBLANK ( _Prev ), BLANK (), _FinalHours & ":" & _FinalMinutes )

Tenga en cuenta que la medida anterior tiene formato de texto (ya que no puede tener un valor de tiempo donde las horas > 24). Entonces, básicamente, esto es útil para mostrar en tablas o matrices, pero no se puede usar para cálculos o en imágenes que requieren valores numéricos.

Si necesita hacer cálculos de la necesidad de mostrar la diferencia de tiempo en imágenes que requieren valores numéricos, deberá usar la diferencia en minutos:

Diff in minutes =
VAR _Prev =
    CALCULATE (
        MAX ( fTable[Transaction time] ),
        FILTER (
            ALLEXCEPT ( fTable, fTable[Salesman] ),
            fTable[Transaction time] < MAX ( fTable[Transaction time] )
        )
    )
VAR _MinutesDiff =
    DATEDIFF ( _Prev, MAX ( fTable[Transaction time] ), MINUTE )
RETURN
    _MinutesDiff

Por supuesto, puede incluir la medida HH:MM en la información sobre herramientas:

He adjuntado el archivo PBIX de ejemplo

[Syndicate_Admin]

Oye, he probado tu solución para recuperar el tiempo de transacción anterior, y aunque funciona, también está funcionando mal.

Tardó 24 segundos en cargar la tabla completa (solo el nombre, el tiempo de transacción y la transacción anterior) en el conjunto de datos de prueba de filas de 15k. En el conjunto de 40k se quedó sin memoria, y mi conjunto de datos real es de 500k y podría crecer a 1mln + en el futuro.

Entonces, ¿asumo que significa que tengo que volver a mis datos originales?

He intentado algo diferente mientras tanto. Volví a la consulta de energía y agregué id de transacción a mis transacciones ordenadas por tiempo de transacción:

Luego he duplicado el ID de vendedor / transacción en una nueva tabla, agrupado por vendedor y terminé con una lista de ID de transacción ordenados por fecha y hora, que luego he ampliado de nuevo:

Hasta este punto, todo funciona sin problemas en el conjunto de datos de 500k, pero ahora viene el cuello de botella:

#"Added Custom1" = Table.AddColumn(#"Sorted Rows1", "Custom", each [Transactions List]{List.PositionOf([Transactions List],[Transaction ID])+1})

Devuelve datos adecuados, que ahora es muy fácil de transformar en tiempo de transacción y luego, con funciones que ha realizado para diferencia horaria. Sin embargo, tomó alrededor de 2 minutos en el conjunto de datos de 40k, lo cual es una mejora, pero obviamente sigue siendo demasiado lento para mis datos reales, ya que uno de los vendedores tiene 60k transacciones, lo que significa que la lista de 60k de longitud tiene que buscar a través de ...

[Syndicate_Admin]

De acuerdo, pruebe un enfoque diferente en Power Query. Básicamente implica crear una sola tabla con el tiempo de transacción y el siguiente tiempo de transacción. Luego deje los cálculos a las medidas (que son simples ya que ambos tiempos de transacción están en la misma fila de la tabla.

Para hacer esto, debe ordenar la tabla por vendedor y tiempo de transacción (en orden ascendente) y agregar una columna de índice a partir de 1

Ahora duplique la tabla y cambie el orden del índice para que comience en 0

Ahora puede combinar ambas tablas seleccionando vendedor e índice, y mantener solo la nueva fila de transacciones de la segunda tabla:

(Puede deshabilitar la carga para la segunda tabla ya que no la necesita en el modelo)

Si prefieres hacer todo este proceso con una sola consulta, aquí está el código M:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WCsnPVdJRMjLSNzDUNzIwMlIwNLIyMFCK1cEmZQqT8s1MzkhMzQFJGyOkTfBLm1kZoxiMJGVuZWKKR6cFmpNMsLgWSacJfhebkGYwVMoUWRfII7EA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Salesman = _t, #"Transaction time" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Salesman", type text}, {"Transaction time", type datetime}}
  ),
  #"Sorted Rows" = Table.Sort(
    #"Changed Type",
    {{"Salesman", Order.Ascending}, {"Transaction time", Order.Ascending}}
  ),
  #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
  Source1 = Source,
  #"Changed Type1" = Table.TransformColumnTypes(
    Source1,
    {{"Salesman", type text}, {"Transaction time", type datetime}}
  ),
  #"Sorted Rows1" = Table.Sort(
    #"Changed Type1",
    {{"Salesman", Order.Ascending}, {"Transaction time", Order.Ascending}}
  ),
  #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 0, 1, Int64.Type),
  #"Merged Queries" = Table.NestedJoin(
    #"Added Index",
    {"Salesman", "Index"},
    #"Added Index1",
    {"Salesman", "Index"},
    "Next transaction",
    JoinKind.LeftOuter
  ),
  #"Expanded Next Transaction" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Next transaction",
    {"Transaction time"},
    {"Next transaction"}
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Expanded Next Transaction", {"Index"})
in
  #"Removed Columns"

Ahora las medidas son mucho más sencillas:

Difference in Minutes =
DATEDIFF (
    MAX ( 'TD on rows'[Transaction time] ),
    MAX ( 'TD on rows'[Next transaction] ),
    MINUTE
)

Diff vs Next transaction time (HH:MM) =
VAR _MinutesDiff =
    DATEDIFF (
        MAX ( 'TD on rows'[Transaction time] ),
        MAX ( 'TD on rows'[Next transaction] ),
        MINUTE
    )
VAR _FinalHours =
    INT ( DIVIDE ( _MinutesDiff, 60 ) )
VAR _FinalMinutes =
    FORMAT ( MOD ( _MinutesDiff, 60 ), "00" )
RETURN
    IF (
        ISBLANK ( MAX ( 'TD on rows'[Next transaction] ) ),
        BLANK (),
        _FinalHours & ":" & _FinalMinutes
    )

Nuevo archivo adjunto

[Syndicate_Admin]

¡Funciona muy bien ahora! Lleva un tiempo más cargar datos, pero las medidas y las imágenes se calculan al instante 😎 ¡Gracias por la ayuda y su gran contribución a la comunidad!