Calendarios personalizados (I).
a) Introducción
La inteligencia temporal de Power BI es una de las características más importantes y que más potencia otorgan a la aplicación. Sin embargo, funciones como DATEADD, DATESYTD, TOTALYTD, SAMEPERIODLASTYEAR, etc., etc., sólo funcionan y tienen sentido cuando trabajamos con el año natural.
Sin embargo, es muy común en muchas organizaciones de diferentes sectores, la utilización de calendarios personalizados para el análisis temporal de la información por semanas con la adopción de calendarios por semanas completas independientemente de la jerarquía temporal tradicional de año, trimestre, mes y día.
Este tipo de calendarios, dividen el año en 4 trimestres de 13 semanas completas que pueden agruparse como 4-4-5, 4-5-4 o 5-4-4.
La principal ventaja de este tipo de calendarios es que la fecha de finalización del período siempre es el mismo día de la semana, lo cual resulta útil para la planificación de turnos o fabricación ya que cada período es de la misma duración.
Por otro lado, la desventaja principal de este tipo de calendarios es que los informes de comparación mensuales tienen fallos, en cuanto que un mes es un 25% más largo que los otros dos y además, los años son de 364 días, lo que significa que es necesario generar una semana 53 cada 5 ó 6 años.
No es el objeto de este post discutir sobre las ventajas o inconvenientes de la utilización de estos calendarios, sino describir cómo podemos utilizarlos en Power BI, para lo que me dispongo a realizar una serie de 3 posts en los que trataré los siguientes aspectos:
1º La creación de un calendario personalizado 4-4-5 en Power Query paso a paso
2º La integración del calendario personalizado en nuestra dimensión fecha y la creación de los períodos en nuestra dimensión período en DAX a través de la creación de los desvíos en el calendario personalizado
3º Dado que como decíamos al utilizar un calendario personalizado dejan de tener sentido las funciones de inteligencia temporal de Power BI, aprenderemos las fórmulas necesarias para la comparación de períodos y la realización de acumulados, supliendo las fórmulas de inteligencia temporal SAMEPERIODLASTYEAR o TOTALYTD
b) Creación en Power Query de un calendario personalizado 4-4-5 paso a paso
Vamos a crear paso a paso nuestra tabla de calendario personalizada.
En primer lugar, vamos a crear tres parámetros:
StartYear, un parámetro de fecha que nos permitirá establecer la primera fecha de nuestro calendario.
FirstFY, primer año fiscal ya que puede no coincidir con el año natural. Por ejemplo si queremos crear el calendario a partir del año fiscal 2019, la primera fecha del calendario sería el 31/12/2018, pero el primer año fiscal sería el 2019.
NYears, el número de años que tendrá nuestro calendario personalizado
A partir de estos parámetros crearemos nuestra lista de días. En una consulta en blanco comenzaremos por establecer el número de días que queremos propagar nuestro calendario a partir de la fecha inicial.
let
NumeroDias= 364*NYears
Origen = List.Dates(StartDate,NumeroDias,#duration(1,0,0,0))
In
Origen
Nuestros años en este calendario serán siempre de 364 días excepto cuando queramos establecer una semana 53.
A partir de ese parámetro NumeroDias que utiliza el parámetro NYears que habíamos creado y el parámetro StartDate en el que establecemos la primera fecha de nuestro calendario, creamos nuestra lista consecutiva de días, que es lo que pretendemos siempre en una tabla de calendario.
Vamos a establecer los parámetros para crear nuestra tabla:
En NYears vamos a poner 5
En StartDate pondremos 4/01/2016
Y en FirstFY pondremos 2016 que es el año fiscal desde el que arrancamos
Esto nos creará una lista de días desde el 4/01/2016 con 364*5= 1820 días, hasta, por tanto, el 27/12/2020.
Los siguientes pasos serán convertir esa lista en tabla, cambiar el tipo de datos a Fecha y renombrar
|
A partir de este punto, comenzaremos a agregar una serie de columnas personalizadas necesarias para nuestro calendario personalizado:
1º Agregaremos un DayID, un índice personalizado desde 1
Que convertiremos en número entero
2º Agregaremos un YearId,
Redondeando hacia abajo la división de nuestro DayId-1 entre 364 y sumando 1 al resultado
3º Agregaremos un QuarterId,
Redondeando hacia abajo nuestro DayId-1 entre 91 y sumando 1 al resultado ya que nuestros trimestres serán regulares de 91 días
4º Agregaremos nuestro MonthId con la siguiente fórmula:
Esta es la fórmula que utilizaremos para el calendario 445.
Si queremos que el calendario sea 454 la fórmula será la siguiente:
= Table.AddColumn(#»Added Custom1″, «MonthId», each Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=28 then 1
else if Number.Mod([DayID],91)<=63 then 2
else 3), Int64.Type)
Y si queremos el calendario 544 la fórmula quedará del siguiente modo:
= Table.AddColumn(#»Added Custom1″, «MonthId», each Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=35 then 1
else if Number.Mod([DayID],91)<=63 then 2
else 3), Int64.Type)
El MonthId, por tanto es el que determina nuestro tipo de calendario que siempre será de trimestres de 13 semanas y 91 días pero que cambia en cuanto a la posición del mes con 5 semanas.
5º Agregaremos nuestro WeekId, de la siguiente forma:
Redondeando hacia abajo el (DayId-1)/7 y sumándole 1.
Una vez que tenemos todos los Id necesarios, ya estamos en disposición de calcular todos los atributos de nuestra tabla de calendario:
1º) El Fiscal Year
= Table.AddColumn(#»Added Custom3″, «FY», each FirstFY-1+[YearId], Int64.Type)
A partir de nuestro parámetro FirstFY restándole 1 y sumándole el YearId obtenemos nuestro año fiscal.
2º) El Fiscal Year Name
= Table.AddColumn(#»Added Custom4″, «FYName», each «FY»&Text.From([FY]), type text)
Con esta fórmula obtenemos el nombre del año fiscal en el formato “FY2019”
3º) El Fiscal Quarter
= Table.AddColumn(#»Added Custom5″, «FQ», each [QuarterId]-(4*([YearId]-1)),Int64.Type)
4º) El Fiscal Quarte Name
= Table.AddColumn(#»Added Custom6″, «FQName», each [FYName]&»-«&»Q»&Text.From([FQ]), type text)
5º) El Fiscal Month
= Table.AddColumn(#»Added Custom7″, «FM», each [MonthId]-(12*([YearId]-1)),Int64.Type)
6º) El FM-MesNombre
= Table.AddColumn(#»Added Custom8″, «FMMesNombre», each if [FM] = 1 then «JAN» else if [FM] = 2 then «FEB» else if [FM] = 3 then «MAR» else if [FM] = 4 then «APR» else if [FM] = 5 then «MAY» else if [FM] = 6 then «JUN» else if [FM] = 7 then «JUL» else if [FM] = 8 then «AUG» else if [FM] = 9 then «SEP» else if [FM] = 10 then «OCT» else if [FM] = 11 then «NOV» else if [FM] = 12 then «DEC» else null)
7º) FM en texto con 2 dígitos
El FM que nos devuelve la serie (1..12) es recomendable transformarlo en 01, 02, 03, 04… etc.
Para ello agregaremos un 0 como prefijo y extraeremos los 2 últimos caracteres.
8º) FM EjercicioMes
= Table.AddColumn(#»Extracted Last Characters», «FMEjercicioMes», each [FYName]&»-«&»M»&[FMNumText])
9º) FW
= Table.AddColumn(#»Changed Type3″, «FW», each [WeekId]-(52*([YearId]-1)),Int64.Type)
Igual que en el mes, este fiscal week que nos construye una serie desde 1..52 lo transformaremos en 01, 02, 03… etc.:
#»Inserted Prefix» = Table.AddColumn(#»Renamed Columns1″, «FMNumTex», each «0» & Text.From([FMNum], «es-ES»), type text),
#»Extracted Last Characters» = Table.TransformColumns(#»Renamed Columns2″, {{«FMNumText», each Text.End(_, 2), type text}}),
Éstas dos transformaciones lo que nos hacen es agregar un prefijo 0 a toda la serie de semanas y luego extrae los dos últimos dígitos
10º) FW EjercicioSemana
Esta columna personalizada nos devuelve el nombre Ejercicio semana en el siguiente formato:
11º) DayinYear
Por último nos faltaría insertar el número de día en el año con la siguiente transformación:
= Table.AddColumn(#»Added Custom11″, «DayinYear», each [DayID]-(364*([YearId]-1)),Int64.Type)
Con esto tendríamos ya nuestro calendario personalizado 445 prácticamente listo.
En próximos posts aprenderemos a cómo trasladar este calendario a nuestra dimensión fechas, como crear nuestros períodos en la dimperiodo y como realizar los cálculos temporales sin la utilización de las fórmulas de inteligencia temporal que como dijimos al principio, no pueden ser usadas si no trabajamos con el año natural.
Adjunto un fichero con el código completo necesario para reproducir la tabla de calendario 445:
