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.

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.

  1. Hola, como estas. agradecido por la ayuda que haces con estas guias y ejemplos.
    Me puedes ayudar respecto de que hice una consulta DirectQuery a la BDD en donde me entrega entre varios datos, la fecha_hora de los registros. Esta fecha hora viene directo claramente y en los campos si bien lo detecta como tal, no me permite hacer jerarquia de años, meses y otros.
    Intente hacer lo que tu haces, pero si bien pude crear la tabla DATE, al vincularla con la mia no me arroja datos posteriormente, ya que los campos de DATE quedan con las hh:mm:ss en 00:00:00 y mis datos traen esos datos, por tanto no sé como poder jerarquizar los datos.
    Lo que quiero es hacer un grafico donde muestre un promedio de valores en columnas de dia-mes, por que asi como esta me los muestra por tiempo y no es lo que necesito.
    Agradecido, espero haberme explicado.

    • Hola Claudio, tendrás que separar fechas y horas en dos campos:
      Internamente los campos fecha creo que son float donde la parte entera es la fecha y la decimal es la hora, por eso enlazar por fecha-hora no te va ser fácil, pues una tabla de fechas ha de hacer un join exacto. Enlazas fecha con fecha y la hora la tendrás que tratar separadamente.
      Saludos

Deja un comentario

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