Dimensión período

Dimensión período

La dimensión período es un conjunto de fechas agrupadas por períodos que permiten filtrar nuestros informes. En nuestro anterior post veíamos cómo elaborar el calendario de fechas en DAX y establecíamos una serie de columnas calculadas a las que llamábamos desvíos (offsets) y ya advertíamos que los desvíos nos servirían para establecer muchos cálculos dinámicos en función del día de hoy.

En este post, vamos a ver como elaborar la dimensión período, qué características tiene y como funciona en nuestros informes.

La primera vez que vi la utilización de la dimensión período fue en el blog de Chris Webb https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi-using-bidirectional-cross-filtering-and-m/ y me quedé maravillado con su potencial. Consistía en establecer una tabla dimensional de períodos temporales tales como el ejercicio actual, el trimestre actual, el mes actual, el día de ayer o el de hoy que podíamos usarlos como filtros en un informe, de manera que, con un solo cálculo de ventas y filtrado por esa dimensión, podíamos obtener el total de las ventas en el contexto temporal seleccionado y además, dinámicamente, según vaya cambiando el día de hoy, el período temporal va cambiando.

Chris Webb en ese post de referencia, explicaba cómo crear la dimensión período en lenguaje M. Yo en esta entrada voy a explicar cómo crearla en DAX. ¿Es mejor crearla en DAX o en M? Con la tabla de fechas pasa lo mismo, tenemos la opción de crearla en M o como expliqué en el anterior post, podemos crearla en DAX. No es mi propósito valorar cuál es la forma más adecuada de hacerlo, yo simplemente prefiero este tipo de tablas crearlas en DAX y por ello explico cómo hacerlo pero ambas formas son totalmente válidas. 

Nuestra tabla Período va a contener 3 campos: El campo Fecha, que incluirá todas las fechas que abarcan un período determinado, el campo NombrePeriodo, que será el nombre descriptivo del período y el Id, que podremos utilizar si queremos simplificar cálculos en los que utilicemos la dimensión período.

 Antes de comenzar a crear y a analizar nuestra tabla Periodo tendremos que crear una tabla auxiliar de fechas. Esto se debe a que si utilizamos la tabla de fechas directamente para crear la tabla de períodos, cuando posteriormente establezcamos la relación entre la tabla de fechas y la tabla de períodos se producirá una referencia circular. Eso lo salvamos creando una tabla auxiliar de fechas, que podremos ocultar ya que sólo nos servirá como base para la creación de la tabla período.

En el post anterior https://powerbisp.com/creacion-de-la-dimension-calendario-en-dax creábamos la tabla de fechas completa a partir de una variable de tabla realizada con el CALENDAR. Debemos coger el mismo rango de fechas que pusimos en esa variable para crear nuestra tabla de fechas auxiliar:

dFechasAux =  CALENDAR(DATE(2013,1,1);DATE(2021,12,31))

Una vez que tengamos nuestra tabla de Fechas Auxiliar, la relacionaremos con nuestra tabla de fechas en una relación que puede ser 1 a 1 en ambas direcciones, ya que en esencia, la tabla auxiliar contendrá las mismas fechas que la tabla de fechas. Ocultamos la Tabla de Fechas auxiliar y ya podemos empezar a construir nuestra dimensión Periodo.

Para la creación de nuestra tabla de Periodos utilizaremos tres fórmulas fundamentalmente:

UNION ya que vamos a ir creando cada periodo como una tabla independiente que uniremos con esa función.

SELECTCOLUMNS para seleccionar las tres columnas que queremos construir (Fecha, NombrePeriodo e Id)

CALCULATETABLE para seleccionar las fechas filtradas que engloban cada período y que nos servirá como la referencia de tabla del SELECTCOLUMNS.

Veámoslo paso a paso.

Si queremos construir el período Ejercicio Actual, en nuestra tabla de fechas teníamos un campo que denominábamos [desvio ejercicio]. Si el [desvio ejercicio]=0 estamos filtrando el ejercicio actual. Por tanto, para construir la tabla periodo del ejercicio actual la fórmula sería:

SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=0);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio Actual”;”Id”;1)

En esta fórmula le estamos diciendo que nos devuelva todas las filas del campo Fecha de la tabla filtrada dFechasAux donde el Desvio Ejercicio =0; una segunda columna “NombrePeriodo” que siempre será “Ejercicio Actual” y una tercera columna “Id” que siempre será 1

Para el Ejercicio Anterior o E-1 simplemente debemos cambiar el valor del “Desvío Ejercicio” por -1

SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=-1);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio E-1″;”Id”;2)

 Con el UNION unimos ambas tablas

UNION(SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=0);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio Actual”;”Id”;1); SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=-1);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio E-1″;”Id”;2))

De esta forma y utilizando los diferentes desvíos como filtro de la tabla de fechas, podemos hacer tantos períodos como podamos imaginar. He aquí un ejemplo de tabla más o menos completa de períodos:

dPeriodo =
UNION (
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio Actual",
        "Id", 1
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio E-1",
        "Id", 2
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio E-2",
        "Id", 3
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Ejercicio] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio Actual hasta hoy",
        "Id", 4
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Actual",
        "Id", 5
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Trimestre] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Actual hasta hoy",
        "Id", 6
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-1",
        "Id", 7
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-2",
        "Id", 8
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -3 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-3",
        "Id", 9
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes Actual",
        "Id", 10
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Mes] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes Actual hasta hoy",
        "Id", 11
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes M-1",
        "Id", 12
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes M-2",
        "Id", 13
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Semestre] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Semestre Actual",
        "Id", 14
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Semestre] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Semestre Actual hasta hoy",
        "Id", 15
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -364, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 365 Dias",
        "Id", 16
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -29, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 30 Dias",
        "Id", 17
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -6, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 7 Dias",
        "Id", 18
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Dia] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Hoy",
        "Id", 19
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Dia] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ayer",
        "Id", 20
    )
)
**Formateado con daxformatter.com y apto para pegar directamente en 
Power BI con el sistema de DAX angloamericano.

*** Ante el cambio de política de Microsoft PowerBI con la idea de unificar el código DAX y la recomendación de utilizar el sistema angloamericano(update mayo-2020), a partir de este post intentaré utilizar siempre el sistema angloamericano, con (,) como separador, aunque me pese ya que siempre he sido de la opinión de conservar el sistema europeo. En las explicaciones el código puede estar con «;» (la fuerza de la costumbre) y por temas de wordpress las «(comillas) no son compatibles por lo que si vais a copiar directamente en Power BI utilizad sólo el código que marque para ello o los ficheros de descarga que adjuntaré.

La dimensión periodo se relaciona con nuestra tabla de Fechas Aux con una relación de varios a 1 pero en ambas direcciones.

De esta manera, las relaciones entre las diferentes tablas de fecha y calendario quedaría con el siguiente esquema:

Por último es necesario realizar una puntualización en cuanto a los cálculos del modelo ya que cualquier cálculo temporal que queramos realizar, deberá contener el filtro ALL(dPeriodo)

Por ejemplo el cálculo normal de Ventas E-1 (Ventas del ejercicio anterior) sin dimensión periodo sería:

Ventas E-1 = CALCULATE(SUM(Sales[SalesAmount]),DATEADD(dFechas[Fecha],-1,YEAR))

 Con la dimensión período, si queremos que nos funcione cuando filtramos por cualquier período quedaría así:

 Ventas E-1 = CALCULATE(SUM(Sales[SalesAmount]),ALL(dPeriodo),DATEADD(dFechas[Fecha],-1,YEAR))

NUEVAS CONSIDERACIONES SOBRE LA DIMENSÍON PERÍODO

Este post lo escribí por primera vez en abril de 2018 y desde entonces he utilizado siempre la Dimensión período creada en DAX en mis modelos, sin embargo en los últimos tiempos me he decantado más por utilizar los desvíos de mi calendario directamente en las medidas o en los slicer para filtrar dinámicamente el tiempo, ambas técnicas están más que probadas y ya es cuestión de la utilización que queramos darle.

Si es cierto que esta técnica en cuanto a performance no es de las más optimas. No he tenido problemas en datasets grandes con ella, pero pueden tenerse.

En octubre de 2019 Phil Seamark hablaba sobre ello en un post: https://dax.tips/2019/10/09/row-based-time-intelligence/ basándose en la dimensión período creada en M que no necesita de la tabla auxiliar y planteando el siguiente esquema de relación como más óptimo:

Esquema propuesto por Phil Seamark en su artículo. Time Intelligence equivaldría a mi dimensión período, pero en esta caso creada en M

Ciertamente he de confesar que no he probado esta técnica propuesta por Phil en mi modelo con la Dimensión período en DAX, pero prometo en cuanto pueda hacerlo y ofrecer mis propias conclusiones. No obstante he de decir que el artículo de Phil es muy recomendable y que explica en gran medida los beneficios (y las desventajas) de una dimensión como ésta y me enorgullece que sea una práctica habitual en mis modelos desde hace muchísimo tiempo y aceptadas por la comunidad de Power BI aunque no conocidas por todo el mundo.

*Os recuerdo que el DAX Angloamericano es con la separación con «,» y europeo con «;» y que desde la versión de mayo de Power BI se puede elegir entre uno u otro.

FranM

Deja un comentario