Crear tablas de fechas con DAX para Time Intelligence

Cualquier proyecto de Business Intelligence suele requerir análisis temporal de los datos: Por años, trimestres, meses, semanas o días. DAX incluye funciones de Time Intelligence que facilitan de modo nativo los cálculos que usan fechas o hacer comparaciones interperiodales fácilmente. También, la funcionalidad Questions&Answers de Power BI utiliza T.I.

Dentro del entorno PowerBI o PowerPivot (o PowerQuery) para el correcto funcionamiento de estas funcionalidades T.I. se requiere disponer, al menos, de una tabla de fechas, y podemos obtenerla de varias maneras, cada una con sus pro-contras.

Dentro de un mismo proyecto podemos tener todas las tablas de fecha que podamos necesitar pero si podemos utilizar el mínimo posible, incluso si es posible solo una, mejor, por simplicidad del modelo, de relaciones, de filtros en reports y cálculos DAX. Esto lo determinará, por supuesto, las necesidades del proyecto a desarrollar.

¿Qué alternativas tenemos?

Podemos partir de una columna de fecha de una tabla de hechos en nuestro proyecto pero hay que tener en cuenta un requisito para el correcto funcionamiento de T.I.:

No puede haber saltos entre las fechas, deben existir todos los días secuencialmente en esta columna, si no T.I. fallará.

Si estamos con un diario de contabilidad no es fácil que tengamos asientos registrados todos los días y quizá no sea aconsejable pensar que con seguridad sí que va a ser así. Aunque limitada, no deja de ser una opción y en un modelo rápido (como alguno de los ejemplos que puedes ver en este blog) puede ser operativo.

Hay quien tiene sus tablas de fechas con sus campos año, mes, trimestres, etc… precalculados,  y las agrega a sus proyectos, las relaciona con el resto de tablas y a analizar. Como ventaja, también permite relacionar con diferentes tablas del modelo de datos que contengan cada una su campo de fecha, o incluso con varios campos fecha dentro de una misma tabla, aunque esto requiere un conocimiento previo de cómo funcionan las relaciones en PowerBI. Es otra opción. Pero requiere mantener una tabla más, añadirla a nuestro modelo de datos, etc… También se puede justificar si accedemos a un Datawarehouse cuyos datos se analizan en otros entornos diferentes a Power BI.

En ambos casos es importante especificar el campo correspondiente, si es que el Query Editor no lo ha detectado, como tipo Datetime.

También podemos recurrir a funciones DAX para crear una tabla de fechas automáticamente. Lo vemos a continuación.

Creación automática usando la función CALENDAR

Comenzamos creando una nueva tabla (“New Table”) y en el cuadro de funciones especificaremos una expresión DAX que llenará esta tabla.

Usamos la función CALENDAR que sólo necesita dos parámetros: fecha desde y fecha hasta:

Tabla= CALENDAR ("01/01/2015";"31/12/2016")

El formato fecha tendrá en cuenta la configuración regional y debe ir entrecomillado. Si quieres obviar inconvenientes en este sentido puede usarse la función DATE:

Tabla= CALENDAR (DATE (2015; 1; 1), DATE (2016; 12; 31))

Como precaución, asegúrate de que la fecha hasta es superior a desde.

Esta simple instrucción agrega la columna “Date” a la nueva tabla y la llena con todos los días comprendidos entre el rango definido, sin huecos, uno de los requisitos a cumplir para poder operar con Time Intelligence.

Y como tabla de pleno derecho que es, vamos a poderla relacionar con otras tablas del proyecto, añadir columnas, cambiar nombres, formatearla…

En estas circunstancias nos vemos obligados a cambiar manualmente las fechas, que en determinados proyectos puede ser suficiente. Pero podemos hacer que el rango desde-hasta sea variable y olvidarnos de este mantenimiento manual. Para ello podemos basarnos en la columna de fechas de alguna de las tablas de hechos de nuestro proyecto. Supongamos que tenemos una tabla “Facturas” con un campo “fecha de emisión”: Podemos partir de las fechas mínima (desde) y la máxima (hasta) de este campo y crear ésta tabla “Fechas”, añadiendo o quitando días automáticamente de la misma, caso de ser necesario, cada vez que se actualicen los datos. Las funciones MAX y MIN se encargan de ello:

Fechas= CALENDAR (
     MIN ( Facturas[Fecha de emision] );
     MAX ( Facturas[Fecha de emision] )
 )

Como alternativa a MAX-MIN, y sirva como inciso, podríamos utilizar las funciones FIRSTDATE y LASTDATE para reemplazarlas, obteniendo el mismo resultado:

Fechas= CALENDAR (
     FIRSTDATE ( FACTURAS[FECHA EMISION] );
     LASTDATE ( FACTURAS[FECHA EMISION] )
 )

Ambos pares de funciones nos devuelven el mismo resultado, pero el tipo de dato en el que lo hace es diferente:

• MAX, MIN devuelven un valor.
• LASTDATE y FIRSTDATE devuelven una tabla con una columna y con una fila como máximo, o vacía.

Por ejemplo, COUNTROWS espera una tabla como argumento; te dará error con MAX-MIN y devolverá un valor o BLANK con las otras. Es importante saber la diferencia por que depende de para qué te convendrá usar unas u otras. En otros artículos lo veremos con mayor detalle.

Pero los proyectos suelen ser más complejos. Siguiendo con el ejemplo de las facturas, puede ser necesario efectuar un previsión de cobros para gestionar la tesorería y para ello se requiere conocer fechas vencimiento o cobro previsto. Lo normal es que esta fecha sea bastante posterior a la de emisión y con MAX de la fecha de emisión van a quedar fechas de cobro fuera del rango. Mejoremos nuestra fórmula, de nuevo usando MAX y MIN:

Fechas= CALENDAR (
     MIN ( MIN ( Facturas[FECHA VENCIMIENTO] ); MIN ( Facturas[FECHA EMISION] ) );
     MAX ( MAX ( Facturas[FECHA VENCIMIENTO] ); MAX ( Facturas[FECHA EMISION] ) )
 )

La tabla resultante podremos vincularla con ambos campos, como puede verse en la imagen, y tenemos la seguridad de que todas las fechas están relacionadas.

A este respecto hay que recordar que Power BI sólo permite una relación activa al mismo tiempo. Te sugiero que leas este post para salvar este supuesto inconveniente.

La función CALENDARAUTO

Al igual que la funcion CALENDAR devuelve una tabla con una columna de fechas automáticamente. Anteriormente especificábamos fechas desde-hasta como parámetros. Esta detecta estas fechas mínima y máxima evaluando las que hayan en campos fecha (no calculados) del modelo.

Sólo espera un parámetro, un entero del 1 al 12, que determina el número de meses, supongamos 3, que se anticipará al fecha mínima (si era 1/5/2017 será 1/2/2017) y que se sumará a la fecha máxima (si era 1/5/2018 será 1/8/2017).

Es un buen punto de partida pero personalmente prefiero tener pleno control sobre lo que hago y no la uso, pero por no dejar de mencionarla.

Formateando la fecha

Este paso que describo ahora suelo realizarlo con prácticamente todos los campos que los requieran en el proyecto, aplicando el tipo de dato que corresponda, fecha, textos, enteros…, Si no se ha detectado adecuadamente, y/o si el formato mostrado no es el deseado.

En este caso, uses CALENDAR o CALENDARAUTO habrás obtenido el mismo resultado una tabla con una columna Date, la cual incluye la secuencia de días dentro del rango definido, pero también incluirá la hora “0:00:00”. No es algo que afecte al funcionamiento o a las relaciones entre tablas, pero me gusta dejar limpios los datos que posteriormente voy a manejar. Por ello seleccionaremos la columna en cuestión y cambiaremos el tipo de dato y/o su formato, como se muestra en la imagen.

Para las fechas me gusta usar el formato “YYYY-MM-DD”… Es una manía heredada de mis experiencias en programación y con bases de datos que seguramente no se justifican en Power BI, pero que de entrada evita confusiones derivadas de configuración regional de fechas (americanos usan mm/dd/yy y europeos dd/mm/yy). Pero puedes usar la que quieras: Lo importante es que el formato no va a afectar a los cálculos.

En un próximo post seguiremos explorando como ir mejorando nuestra tabla de fecha para explotar al máximo todas las posibilidades que T.I. nos ofrece.

Santiago Tomás

Apasionado de las tecnologías de análisis de datos y Business Intelligence, y con larga experiencia en el uso de las mismas en empresas de todos los sectores, tamaños, y proyectos de diversa complejidad, en los cuales disfruto creando, planteando y desarrollando soluciones.
Tengo una curiosidad insaciable y demasiadas aficiones, entre ellas tocar el bajo con mi grupo.