Una reserva hotelera, el tiempo dedicado a una orden de trabajo, la duración de un evento o congreso, la duración de un contrato, las fases de un proyecto, plazos de atención en resolución de incidencias, plazos de expedición… Hay muchas ocasiones en las que determinada información se nos proporciona como un suceso que transcurre entre dos fechas, un periodo desde-hasta en una línea de una tabla. Calcular el número de días entre ambas fechas entre ambas es trivial.

Puede suceder que se requiera afinar afinar el cálculo y retornar el número de días laborables, como vimos en este post, Calcular número de dias laborables entre dos fechas y tabla de festivos “variables”.

Pero es una métrica que nos deja las posibilidades de análisis algo limitada, y en un modelo de datos así hasta la inteligencia temporal de Power BI con la información dispuesta de este modo va a ser compleja. Por ello, vamos a ver cómo reconvertir esta información en una lista de días, lo que permitirá ampliar las capacidades del análisis significativamente.

Nota: En el post Convertir desde-hasta en filas (otro modo, con operador de listas) puedes ver otra alternativa a la que aquí se ofrece.

Los datos de origen

En este caso disponemos de una tabla con Ordenes de producción realizadas por operarios desde un día hasta otro día. Varios operarios pueden participar en una misma orden y en diferentes periodos. Algo así:

A partir del mismo fichero podemos iniciar nuestro proyecto añadiendo una tabla de fechas con Dax y una columna calculada (algo que es preferible evitar usar, pero para ejemplo sirve) que calcule el número de días entre fechas desde-hasta (también lo puedes hacer con una medida dax simple).

La columna calculada puede requerir que sumes un 1 si consideras que tanto el día desde como el día hasta han de computar como laborable. En el caso de una reserva hotelera se cuenta noches por lo que un datediff simple sobraría, sin necesidad de sumar un día, pero no es el caso.

También considerar que puede haber órdenes abiertas (sin fecha Hasta). Si queremos que haga cómputo podemos especificar que si fecha hasta está en blanco haga la diferencia con el día de hoy:

Con esta información podríamos sacar algunos indicadores:

Si relacionamos con tabla de fechas ya nos encontramos con la limitación de con qué fecha ¿con la de inicio, la de fin…? No podemos tener relación activa con ambas al mismo tiempo. Activemos la que relaciona con fecha de inicio:

Si no hacemos otra cosa, como mucho podemos sacar la duración de ordenes con fecha de inicio en un periodo determinado, independientemente de su fecha de fin:

Otros detalles como sólo días laborables, etc., van a ser complicados de extraer.

Lo que necesitamos

Convertir los datos anteriores a filas, tantas filas como días haya entre los periodos desde-hasta, por orden y operario que haya trabajado en ella, algo así:

Esto ya nos proporciona una base de trabajo mucho más rica. Y hacerlo no es tan complicado, pero necesitaremos recurrir a funciones de Power Query.

Punto de partida

Este post de Chris Webb, además de ser genial, ofrece una función que crea dinámicamente una tabla de fechas. Después de leerlo y comprenderlo, verás rápidamente por donde va mi solución.

La función que propone Chris es algo así:

let
Origen = (
PeriodName as text,
StartDate as date,
EndDate as date,
SortOrder as number
) as table =>
let
DayCount = Duration.Days(EndDate-StartDate)+1,
DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddPeriodName = List.Transform(DateList,
each {PeriodName,_,SortOrder}),
CreateTable = #table(
type table[Period=text, Date=date, Sort=number],
AddPeriodName)
in
CreateTable
in
Origen

Y cuando es invocada crea una tabla de fechas con tantas líneas como días hay en el periodo desde-hasta y alguna columna adicional. Lo que necesito.

Yo la adaptaré a mis necesidades y eliminaré de la misma lo que no necesito, que son esas columnas adicionales:

let
Origen = (StartDate as date,
EndDate as date) as table =>
let
DayCount = Duration.Days(EndDate-StartDate)+1,
DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddPeriodName = List.Transform(DateList,
each {_}),
CreateTable = #table(
type table[ Date=date],
AddPeriodName)
in
CreateTable
in
Origen

La bautizaré como “CreatePeriodTable_Modificada”. Además voy a invocarla de un modo diferente, desde la misma tabla de órdenes original. Veámoslo paso a paso.

Los pasos seguidos son…

1-Creo una referencia a la tabla ordenes original, así trabajo con una “copia” de la misma

2-Añado una columna condicional para que rellena con el día de hoy a aquellas órdenes sin fecha hasta. Esto es algo que tú puedes requerir manejar de otro modo, como filtrar estas órdenes para que no computen. Yo sí las quiero:

3-Voy a invocar a la función personalizada que he creado “CreatePeriodTable_Modificada” y que recibe como parámetros la fecha desde-hasta, que definen las columnas de la propia tabla.

4-El resultado de la anterior función va a ser una tabla, que expandiremos y ¡sorpresa, justo lo que buscaba!:

5-Tras eliminar las columnas innecesarias este es el resultado final:

6-Aplicamos cambios y volvemos a diseñador de informes

7-Relacionamos la columna de fecha con tabla fechas:

Analizando

Podemos crear una medida de recuento de días tan simple como la siguiente:

Dias = count('WORK ORDER A DIAS'[DATE])

Pero si queremos una que solo compute días laborables tampoco necesitamos complicarnos demasiado la vida: Un simple filter da el resultado:

Dias Laborables = CALCULATE('WORK ORDER A DIAS'[Dias], not Fechas[DiaSemanaNo] in{1,7})

Aplicar inteligencia temporal con este planteamiento es trivial, y dejo una pequeña muestra de las diferencias entre planteamiento inicial (arriba de la divisoria azul) y final:

Si filtramos fechas la diferencia es mucho más significativa:

Espero que te sea de ayuda.

Te puede interesar...

2 comentarios en «Transformar columnas desde-hasta en filas»

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *