Mejorar tablas de fechas para Time Intelligence

Vimos en una artículo anterior como construir una tabla de fechas dinámicamente, alguna de las normas que rigen su creación y su uso para Time Intelligence de Power BI.

En este post seguimos con el tema, mejorando el proceso y explicando alguna cosa más sobre funciones DAX de fecha.

Añadir columnas a tabla “Date”

En el anterior post llegamos a crear una secuencia de días dentro de un intervalo desde-hasta. El hecho de agregar el campo fecha a los visuals de un report ya crea una jerarquía Año->Trimestre->Mes->Dia automáticamente. De la misma podemos eliminar los niveles que no nos interesen, pulsando la “X” a la derecha del nivel a eliminar en la jerarquía.

En determinados casos esta funcionalidad puede ser suficiente para ofrecer un análisis. Pero si queremos enriquecerlo y añadir posibilidades tendremos que crear estas medidas y otras más explícitamente.

Añadir columnas AÑO y MES con funciones DAX

A nuestra tabla creada con CALENDAR vamos a añadirle columnas. Comenzaremos con “Año”, tan fácil como usar la función “Year”:

Año= YEAR ( [Date] )

Seguimos con columna “Mes”. Queremos el mes como texto, abreviado (Mes) y completo (MesL), pues según la tabla nos puede convenir mostrar uno u otro.

Mes=FORMAT ( [Date], "mmm" )

MesL=FORMAT ( [Date], "mmmm" )

NOTA: La opción “Locale” de Power BI determina el idioma en el que se presenta el nombre del mes.

Por defecto la ordenación de los meses será alfabética y si queremos ordenar por número de mes habrá que crearlo como medida en primer lugar. Te ofrezco dos alternativas, formateada “00” o sin formato:

MesNo = FORMAT ( [Date]; "MM" )

MesNo = month(Fechas[Date])

A continuación seleccionamos las columnas con los literales Mes y MesL y en “Sort by column” u “Ordenar por columna”, elegimos la columna con el número de mes:

Ahora los informes que muestren mes como texto lo harán ordenando correctamente.

Mostrando valores: Particularidades

Tras añadir esta medidas parece evidente cómo van a poderse mostrar los datos. Pero hay que puntualizar alguna cosa…

Supongamos que tenemos una serie con valores desde Enero-2015 hasta  Mayo 2017. Comencemos mostrando un evolutivo mensual:

No es un evolutivo real, pues está representando la suma de todos los meses de todos los años de la serie. Si añadimos “Año” como leyenda se verá mejor la situación:

Este planteamiento no permite, por ejemplo, hacer un análisis de evolutivo más allá de un año ni interanual, ni análisis de regresión… (bueno, sí, pero no tan fácilmente)

Por ello será necesario agregar nueva columnas que permitan puentear este problema. Básicamente vamos a hacer único cada mes, “concatenándolo” con su año:

AñoMes = FORMAT ( [Date]; "YYYY-mmm" )

De nuevo, por el tema de las ordenaciones alfabéticas de los meses, creamos también el correspondiente numérico:

AñoMesNo = FORMAT ( [Date]; "YYYY-MM" )

Esto permitiría mostrar los datos según lo esperado y hacer análisis de tendencias, medias móviles, regresiones… más fácilmente:

Otras funciones

Del mismo modo que hemos añadido año, mes, podremos añadir número de día de la semana, día de la semana, trimestres…:

DiaSemanaNo = WEEKDAY ( [Date])

DiaSemana = FORMAT ( [Date]; "dddd" )

DiaSemanaCorto = FORMAT ( [Date]; "ddd" )

Trimestre = FORMAT ( [Date]; "Q" ) & "T"

Con sus correspondientes concatenaciones con el año, caso de requerirse, y ordenación de literales según número asociado a cada uno, como hemos visto anteriormente.

Haciéndolo todo en un paso

Hemos visto paso a paso cómo crear la tabla de fechas y añadir columnas a nuestra conveniencia. Pero podríamos crear en un solo paso esta tabla y sus campos, unificando en una función DAX todo lo visto:

Fechas = ADDCOLUMNS ( 

calendar(min(pyg_centros[Fecha]);max(pyg_centros[Fecha])); 

"Año"; YEAR ( [Date] ); 

"MesNo"; FORMAT ( [Date]; "MM" );

"AñoMesNo"; FORMAT ( [Date]; "YYYY/MM" ); 

"AñoMes"; FORMAT ( [Date]; "YYYY/mmm" ); 

"Mes"; FORMAT ( [Date]; "mmm" );

 "MesL"; FORMAT ( [Date]; "mmmm" ); 

"DiaSemanaNo"; WEEKDAY ( [Date] ); 

"DiaSemana"; FORMAT ( [Date]; "dddd" ); 

"DiaSemanaCorto"; FORMAT ( [Date]; "ddd" ); 

"Trimestre"; FORMAT ( [Date]; "Q" ) & "T"; 

"AñoTrimestre"; FORMAT ( [Date]; "YYYY" ) & "/T" & FORMAT ( [Date]; "Q" )

)

Para futuros usos bastará con copiar, crear una nueva tabla, pegar el código y cambiar los nombres de los campos donde proceda.

NOTA: Por comodidad aquí tienes la misma medida pero usando comas “,” en lugar de “;” como separador:

Fechas = ADDCOLUMNS (

calendar(min(pyg_centros[Fecha]),max(pyg_centros[Fecha])),

"Año", YEAR ( [Date] ),

"MesNo", FORMAT ( [Date], "MM" ),

"AñoMesNo", FORMAT ( [Date], "YYYY/MM" ),

"AñoMes", FORMAT ( [Date], "YYYY/mmm" ),

"Mes", FORMAT ( [Date], "mmm" ),

"MesL", FORMAT ( [Date], "mmmm" ),

"DiaSemanaNo", WEEKDAY ( [Date] ),

"DiaSemana", FORMAT ( [Date], "dddd" ),

"DiaSemanaCorto", FORMAT ( [Date], "ddd" ),

"Trimestre", FORMAT ( [Date], "Q" ) & "T",

"AñoTrimestre", FORMAT ( [Date], "YYYY" ) & "/T" & FORMAT ( [Date], "Q" )

)

Se haga como se haga el resultado final será similar al siguiente:

Por último…

Si lo necesitamos para análisis Drill, sólo nos quedaría crear las jerarquías en la dimensión Fecha usando las medidas que hemos creado: Basta con arrastrar y ordenar dentro de la dimensión y renombrar como se desee:

En próximos artículos veremos las particularidades de alguna de las funciones utilizadas en este artículo y las posibilidades de análisis que de forma nativa nos proporciona Time Intelligence.

Espero que este artículo te haya sido útil.

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.