Creación de la dimensión Calendario en DAX

Creación de la dimensión Calendario en DAX

Uno de los grandes “poderes” de los modelos analíticos en Power BI es la inteligencia temporal en los cálculos. Ello nos permite que calcular medidas como las ventas y compararlas con períodos de ventas anteriores resulte extremadamente sencillo y potente, pero la base fundamental para ello es disponer de una buena y completa tabla de calendario con todas las columnas calculadas que puedan servirnos para nuestros análisis.

Las tablas de calendario pueden hacerse de diversas formas, desde el editor de Power Query en M o desde el propio entorno de Power BI Desktop con el lenguaje DAX.

Después de usar ambos sistemas y ver las bondades de uno y otro, me quedo con la programación de la tabla de Calendario en DAX y he elaborado una tabla de fechas con todas las columnas calculadas que utilizo en mi día a día en un sólo cálculo de tabla.

A partir de la función de tabla CALENDAR y usándola como variable, puede en un mismo cálculo insertarse cuantas columnas calculadas necesitéis con la función SELECTCOLUMNS.

La tabla definitiva de calendario que he elaborada obedece a la siguiente fórmula, que puede copiarse tal cual en la barra de fórmulas tras presionar “nueva tabla”:

DimFechas =
VAR Fecha =
    CALENDAR ( DATE ( 201911 )DATE ( YEAR ( TODAY () ) + 11231 ) )
RETURN
    SELECTCOLUMNS (
        Fecha;
        «Fecha»; [Date];
        «IdFecha»YEAR ( [Date] ) * 10000
            MONTH ( [Date] ) * 100
            DAY ( [Date] );
        «IdFechaEntero»INT ( [Date] );
        «Ejercicio»YEAR ( [Date] );
        «Mes»UPPER ( FORMAT ( [Date]; «MMMM» ) );
        «MesNro»INT ( FORMAT ( [Date]; «M» ) );
        «NroDia»INT ( FORMAT ( [Date]; «d» ) );
        «Trimestre»«T»
            ROUNDUP ( MONTH ( [Date] ) / 30 );
        «NroTrimestre»ROUNDUP ( MONTH ( [Date] ) / 30 );
        «DiaSemana»WEEKDAY ( [Date]; 2 );
        «Semana»WEEKNUM ( [Date]; 2 );
        «Nombre Dia»UPPER ( FORMAT ( [Date]; «DDDD» ) );
        «EjercicioTrimestre»COMBINEVALUES (
            «-«;
            YEAR ( [Date] );
            «T»
                ROUNDUP ( MONTH ( [Date] ) / 30 )
        );
        «NºSemana ISO»ROUNDDOWN (
            (
                [Date]
                    – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                    WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
            ) / 7;
            0
        );
        «MesCorto»UPPER ( FORMAT ( [Date]; «MMM» ) );
        «DiaEjercicio»UPPER ( FORMAT ( [Date]; «Y» ) );
        «EjercicioActual»YEAR ( TODAY () );
        «Mes Actual»MONTH ( TODAY () );
        «HoyEntero»INT ( TODAY () );
        «Trimestre Actual»ROUNDUP ( MONTH ( TODAY () ) / 30 );
        «Semestre»ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 );
        «EjercicioMes»COMBINEVALUES ( «-«YEAR ( [Date] )FORMAT ( [Date]; «MM» ) );
        «EjercicioISO»IF (
            OR (
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 53;
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 52
            )
                && MONTH ( [Date] ) = 1;
            YEAR ( [Date] ) – 1;
            IF (
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 1
                    && MONTH ( [Date] ) = 12;
                YEAR ( [Date] ) + 1;
                YEAR ( [Date] )
            )
        );
        «Desvio Ejercicio»YEAR ( [Date] ) – YEAR ( TODAY () );
        «Desvio Mes»IF (
            ( YEAR ( [Date] ) – YEAR ( TODAY () ) ) = 0;
            INT ( FORMAT ( [Date]; «M» ) ) – INT ( FORMAT ( TODAY ()«M» ) );
            INT ( FORMAT ( [Date]; «M» ) ) – INT ( FORMAT ( TODAY ()«M» ) )
                + (
                    12
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        );
        «Desvio Dia»INT ( [Date] ) – INT ( TODAY () );
        «HastaFecha»IF ( ( INT ( [Date] ) – INT ( TODAY () ) ) <= 0«PASADO»«FUTURO» );
        «Desvio Trimestre»IF (
            ( YEAR ( [Date] ) – YEAR ( TODAY () ) ) = 0;
            ( ROUNDUP ( MONTH ( [Date] ) / 30 ) )
                 ( ROUNDUP ( MONTH ( TODAY () ) / 30 ) );
            ( ROUNDUP ( MONTH ( [Date] ) / 30 ) )
                 ( ROUNDUP ( MONTH ( TODAY () ) / 30 ) )
                + (
                    4
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        );
        «Semestre Actual»ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 );
        «Desvio Semestre»IF (
            YEAR ( [Date] ) – YEAR ( TODAY () ) = 0;
            (
                ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 )
            )
                 (
                    ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 )
                );
            (
                ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 )
            )
                 (
                    ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 )
                )
                + (
                    2
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        )
    )

El código dax ha sido formateado en https://www.daxformatter.com/ herramienta que os recomiendo para mejor legibilidad del código y os lo podéis descargar en txt en el siguiente enlace:

Además de diferentes columnas muy interesantes de fechas como la Semana ISO o el Ejercicio ISO para calendarios gregorianos, muy usado en nuestras empresas agrícolas, establezco los denominados desvíos (offsests) que introdujo Avi Singh, gurú del power BI americano, que permiten realizar infinidad de cálculos dinámicos en función del día de hoy.

Las fechas de inicio y fin pueden cambiarse fácilmente modificando sólo la tabla Fecha que se define en la variable. A modo de ejemplo he puesto desde el 1/1/2019 al 31/12/2021.

Espero que os sea de utilidad y si tenéis alguna sugerencia de columna que pondríais en la tabla de fechas podéis decírnoslo en los comentarios.

FranM

Deja una respuesta