Después de unas pequeñas y merecidas vacaciones, ¡volvemos con los jueves de post! Que se echaba en falta un poquito de DAX-Power BI.
Siguiendo la encuesta que lancé en su momento, toca empezar con los post relacionados con la inteligencia de tiempos para luego llegar a los grupos calculados.
Para abrir esta serie, hoy vamos hablar de lo necesario para trabajar en Power BI, y no es nada más y nada menos que de cómo crear una tabla de calendario para luego poder trabajar con la inteligencia temporal que nos proporciona Power BI:
Hoy en día, casi cualquier modelo de datos, incluye algún cálculo temporal, por ejemplo: ventas acumuladas, ventas del año anterior… Para poder realizar este análisis, necesitamos una tabla calendario, ya sea creada en Power Query, en DAX o importada desde nuestro DWH.
Si un modelo contiene varias fechas, como la fecha del pedido y la fecha de envío, se pueden crear varias relaciones con una sola tabla de fechas como ya vimos en la serie de post de relaciones o también duplicar la tabla de fechas (práctica no recomendada ya que engorda nuestro modelo y su mantenimiento es más complejo). En cualquier caso, siempre debemos de crear al menos una tabla de fechas siempre que haya una o más columnas de fechas en los datos.
A continuación, y antes de entrar en materia, vamos a ver unas pequeñas buenas prácticas que deberemos tener en cuenta a la hora de diseñar nuestra tabla de tiempo.
- Las fechas que debe incluir la tabla son las fechas que queremos estudiar en nuestro modelo, es decir, si nuestro período de análisis comprende 01/01/2020 al 31/12/2022 nuestra tabla no debería contener fechas ni anteriores ni posteriores ya que lo único que aportan es peso a nuestro modelo. Este problema suele darse cuanto la tabla tiempo viene de un DWH creado por IT.
- Si la tabla de fechas contiene una columna de tipo DateTime , la deberíamos separar en dos columnas, por un lado la parte de la fecha y por otro lado la parte de la hora. Recordar que para el motor de Power BI, la parte de la fecha es el equivalente a un número entero y la parte de la hora a un número decima.
Una vez ya hecho una breve introducción, vamos a ver la función CALENDAR. Como siempre, según la web oficial de Microsoft:
La función CALENDAR devuelve una tabla con una sola columna denominada "Date" que contiene un conjunto contiguo de fechas. El intervalo de fechas va desde la fecha de inicio especificada hasta la fecha de finalización especificada, incluidas las dos fechas.
Cuya sintaxis es:
Donde:
<start_date> es cualquier expresión DAX que devuelva un valor datetime.
<end_date> es cualquier expresión DAX que devuelva un valor datetime.
Bien, ahora vamos a crear nuestra tabla calendario desde 0 para ello:
En la tabla que se nos ha creado asignamos el nombre que le queremos dar a nuestra tabla calendario, en este caso, DimCalendario. Y escribimos la función CALENDAR y como vemos nos pide los dos argumentos (startDate y EndDate)
Queremos crear nuestra tabla DimCalendario entre 01/01/2022 y el 31/12/2022. ¿Qué debemos escribir en StartDate? ¿01/01/2022? ¿Y cómo EndDate? ¿31/12/2022? Vamos a verlo…
Parece que nos la ha creado correctamente ya que el editor de DAX no nos devuelve ningún error ¿no os parece?
Vamos a ver la tabla:
Primero porque el argumento
que le hemos pasado no es del tipo DateTime. Para ello necesitamos recurrir a
la función DATE y anidarla o introducir la fecha entre comillas dobles. Yo soy partidario de usar la función DATE.
Como podemos ver, nos ha creado un campo DateTime, pero las horas en este caso para el análisis no aportan valor ya que son todas 0:00:00 por lo que es recomendable darle el formato correcto, para ello:
Y cambiamos el formato de Fecha y Hora a Fecha:
Ya tenemos nuestra tabla fecha, pero antes de darle formato para poder hacer un análisis profundo de nuestro modelo otro tip de buenas prácticas, no usar palabras reservadas para el nombre de las medidas y/o columnas, por lo que la columna llamada Date vamos a renombrarla con Fecha.
Ahora hacemos una columna de año ¿cómo? Muy sencillo con la función YEAR cuando le pasamos una fecha. ¿Qué fecha? Mi campo Fecha de la tabla DimCalendario:
Tenemos el año, ahora vamos a obtener el número del mes con la función MONTH.
Si queremos tener en nuestro modelo el número del día dentro de la semana que es usamos la función WEEKDAY:
Tipo de valor devuelto: 2, la semana comienza el lunes (1) y termina el
domingo (7).
Tipo de valor devuelto: 3, la semana comienza el lunes (0) y termina el
domingo (6), numerado del 1 al 7.
Por ejemplo, una manera de saber si es fin de semana, ponemos la condición de si es mayor o igual que 6 me devolverá un booleano para quien cumpla la condición.
Bien, y ahora os estaréis preguntando, el número del día de la semana no me aporta gran cosa, necesito saber el nombre del día, para ello vamos a ver 3 maneras distintas comparándolas entre sí.
Lo que nos devuelve la función superior son todos los días de la semana en función de si se cumple la condición, aunque no os recomiendo anidar IF sobre IF por rendimiento yo os la muestro, ya que esto luego nos aporta una visión global de DAX.
Vamos a ver la segunda opción:
En este caso tenemos un SWITCH y este evalúa el resultado devuelto por la función WEEKDAY y voy colocando los nombres de los días. Como se puede observar es una función más sencilla y más óptima que la escrita con la función IF... ¿No os parece?
Vale, la función está muy bien Javi, pero yo no quiero escribir todos los nombres de los días. ¿Cómo lo hago entonces? ¿Hay alguna forma más sencilla Javi de hacerlo? Pues sí, y es con la función FORMAT:
Como podemos ver, el resultado para las 3 formas, es el mismo. Desde mi punto de vista, es más cómodo está última opción, pero como se dice, para gusto colores o con en DAX hay varios caminos para llegar al mismo resultado (Cosecha propia este dicho ;) ):
La función FORMAT, tiene un montón de formatos de string para enviarle, yo desde luego no me los sé todos ni mucho menos, pero si lo tengo en el radar y cuando necesito lo consulto y es lo que os lo recomiendo.
Por ejemplo, si queremos obtener el trimestre del año:
O si queremos saber el nombre del mes:
Y así podemos seguir realizando nuestra tabla calendario, pero… sí, hay un pero, ¿qué pasa si mi modelo aumenta más en fechas que lo que defina en mi tabla calendario? ¿Amplio y amplio mi tabla de calendario? Puede ser una opción, ¿pero veis necesario tener por ejemplo el año 2100 en nuestro modelo? No, ¿no? Para evitar eso hay una solución y es con la función CALENDAR.
Vamos a nuestro modelo de Contoso, y disponemos del siguiente modelo en estrella (recordar que hay que llegar en la medida de lo posible a un modelo en estrella. Para ello os recomiendo el libro imprescindible de Toni Jurado: Fundamentos de modelado en Estrella. Os dejo el link aquí por si lo queréis adquirir. ¡Ójala lo hubiese tenido cuando empecé con Power BI!).
Como podemos ver, tenemos nuestra tabla principal o tabla de hechos FactSales, y esta dispone de las siguientes columnas de tipo Fecha:
A nosotros para nuestro modelo, lo que nos interesa es que nuestra tabla de calendario esté comprendida entre los valores de la columna FechaPedido y FechaEnvio. O lo que es lo mismo, la fecha mínima de realización de un pedido y la fecha máxima de envío de un pedido por lo que escribimos la siguiente sentencia:
Y como podemos ver, Power BI nos ha creado nuestra tabla calendario con la fecha inicial igual a la fecha mínimo del FechaPedido (01/01/2007):
Y la fecha final igual a la máxima de FechaEnvio (06/01/2010):
De esta manera conseguimos que nuestra tabla calendario sea dinámica, es decir, vaya aumentando/disminuyendo según nuestras necesidades.
Y ahora me preguntaréis: ¿Y qué pasa si queremos analizar a futuro? Por ejemplo, presupuestos derivados de los planes de gestión… De esta forma no podemos. Y yo os digo que sí se puede, sólo tenemos que modificar un poco nuestra tabla y en algún momento ya hemos hablado de cómo hacerlo, ¿se os ocurre? Os doy una pista… ¿Cómo trata internamente Power BI los campos de Fecha y Hora? ¡Exactamente! Como números.
A la fecha máxima de envío le hemos sumado un año, por lo que como podemos ver la última fecha ahora es 06/01/2011:
Y para el resto de la tabla calendario, es lo mismo que hemos visto en la parte inicial de este post.
Y antes de terminar la creación de la tabla de calendario mediante DAX, quiero hablar también de la función CALENDARAUTO.
Según la web de Microsoft, la función CALENDARAUTO devuelve una tabla con una sola columna denominada "Date" que contiene un conjunto contiguo de fechas. El rango de fechas se calcula automáticamente según los datos del modelo.
El rango de fechas se calcula de la manera siguiente:
- La fecha más antigua del modelo que no se encuentra en una columna o en una tabla calculada se toma como MinDate.
- La fecha más reciente del modelo que no se encuentra en una columna o en una tabla calculada se toma como MaxDate.
- El rango de fechas devuelto son las fechas comprendidas entre el principio del año fiscal asociado a MinDate y el final del año fiscal asociado a MaxDate.
La sintaxis es muy simple:
Donde [fiscal_year_end_month] es cualquier expresión DAX que devuelva un entero entre 1 y 12. Si se omite, el valor predeterminado es el valor especificado en la plantilla de tabla de calendario del usuario actual, si está presente; de lo contrario, el valor predeterminado es 12.
Una vez dicho esto, vamos a crear la tabla calendario en nuestro modelo con la función CALENDARAUTO.
Y nos devuelve 01/01/1910 como fecha mínima:
Y nos devuelve 31/12/2022 como fecha máxima:
Como resultado, tenemos una tabla calendario sí, pero esta tabla está compuesta de 41.273 filas (112 años). ¿Creéis que es necesario tanto registro para nuestro modelo? Depende, en mi humilde opinión para este modelo no, para otro puede que sí. Esta función es muy útil cuando sólo tenemos una tabla con fechas (por ejemplo: FactSales) y el análisis a realizar es de las ventas, pero si tenemos también tablas de dimensiones con fechas (por ejemplo: DimCustomer) en la que registramos año de nacimiento… pues la tabla empieza a coger un volumen innecesario para este caso, pero puede haber casos en los que sea útil.
Y bueno, para ser el primer post después de vacaciones, ya
es más que suficiente. En los siguientes entraremos en las funciones de inteligencia de tiempo y el los grupos calculados.
¡Nos vemos en los datos!
