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:
pasado… ¿Y por qué os estaréis preguntando? Pues sencillo, por dos motivos:
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.
La función DATE devuelve la fecha especificada en
formato datetime y cuya sintaxis
es:
la fecha:
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.
mes, usamos la función DAY:
Si queremos tener en nuestro modelo el número del día dentro
de la semana que es usamos la función WEEKDAY:
la semana comienza el domingo (1) y termina el sábado (7), numerado del 1 al 7.
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.
False si no la cumple.
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!
