Dimensión Período en DAX

 In Destacado, Power Bi

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://powerbiuniversity.com/calendario-completo-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))

 

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))

 

Descargar dimension periodo en txt

Recommended Posts
Showing 6 comments
  • JOSE ROBERTO TORRES LOPEZ

    Gran aporte es lo que ando buscando ya que necesito generar filtros para diferentes periodos como ejemplo YTD o Moving Anual Total (MAT) o Last 4 Weeks o las 2 weeks que en estos dos ultimos casos no encuentro como filtrar las semana o mejor dicho como establecer la semana, veo YEAR, MONTH, Quarter pero no week.
    Como podría generar Weeks??.

  • Alejandro González Bueno

    Hola Francisco!

    Podrías poner el código en un archivo de texto al igual que en el post de Calendario.

    ¿La tabla AUX también la podrías compartir?

    Muy aporte!

    Gracias!

    • Francisco Mullor Cabrera

      Si, por supuesto, ya lo tienes. La tabla auxiliar tiene que ser igual que la tabla de fechas pero sin todas las columnas, solo con la columna de fecha. Por tanto, si construyes la tabla de fechas con un CALENDARAUTO(12) poues la tabla auxiliar igual y si construyes la tabla de fechas con CALENDAR (DATE(2013;1;1); DATE(2021;12;31)) pues la auxliar igual. Exactamente el mismo código que uses en la variable de la dFechas. En todo caso, lo que quiero es explicar detenidamente su funcionamiento para que cualquier usuario pueda crear el período que necesite ya que ahí sólo están los estandar

  • Cristian Astorga

    Francisco podrías colocar un ejemplo gráfico en power bi de como trabaja esto, me pareció excelente el post, estoy tratando de graficar una serie temporal filtrada por un solo periodo y creo que esto podría ser parte de la solución que estoy buscando.

  • MAURICIO

    Buenos dias profesor Francisco. Muy interesante y util este tema. Implemente paso a paso la tabla calendarios y la tabla de periodos como se explica en el articulo en una aplicacion que estoy desarrollando para fines academicos de control de peso. Tratare de explicar la situacion. Tengo una muestra de datos desde el 5 de enero 2019 hasta hoy junio 29 de 2020. En una visualizacion tengo estas dos medidas:

    PesoPromedio = Average(tblPeso[Fecha])
    PesoPromedioPeriodoFiltro = Calculate([PesoPromedio], AllSelected(tblCalendario)) // Calcula el peso promedio en el periodo determinado por el segmentador de filtro.

    Cuando implemento el filtro por periodos de tiempos la primera medida funciona muy bien….La grafica limita su rango de fechas al periodo filtrado. La segunda medida por el contrario tiene un comportamiento curioso, el valor calculado corresponde al promedio del periodo filtrado, pero en la visualizacion grafica se muestra todo el conjunto de datos desde el primer dato hasta el ultimo, sin aplicar ningun filtro.
    Como puedo solucionar esto?
    Muchas gracias, y un saludo desde Colombia

  • Wilmer Gonzalez

    Buen dia Profesor. Excelente articulo, lo implemente y esta en funcionamiento sin embargo lo que requiero es un corte mes a mes , es decir cada corte tiene que tener un periodo hacia atrás, se debe tomar los datos con histórico de 60 dias hacia atrás, como se implementaría dado esta estructura .
    Agradezco cualquier ayuda con esta situación.
    Muchas Gracias.