Calendario completo en DAX

 In Destacado, Power Bi

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»:

dFechas =

var Fecha = CALENDAR(DATE(2013;1;1);DATE(2021;12;31))
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])/3;0);»NroTrimestre»;ROUNDUP(MONTH([Date])/3;0);»DiaSemana»;WEEKDAY([Date];2);»Semana»;WEEKNUM([Date];2);»Nombre Dia»;UPPER(FORMAT([Date];»DDDD»));»EjercicioTrimestre»;COMBINEVALUES(«-«;YEAR([Date]);»T»&ROUNDUP(MONTH([Date])/3;0));»Nº Semana ISO»;ROUNDDOWN(([Date]-DATE(year([Date]-WEEKDAY([Date]-1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-1)+4);1;3))+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())/3;0);»Semestre»;ROUNDUP(INT(FORMAT([Date];»M»))*2/12;0);»EjercicioMes»;COMBINEVALUES(«-«;Year([Date]);FORMAT([Date];»MM»));»EjercicioISO» ; IF(OR((ROUNDDOWN(([Date]-DATE(year([Date]-WEEKDAY([Date]-1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-1)+4);1;3))+5)/7;0))=53; (ROUNDDOWN(([Date]-DATE(year([Date]-WEEKDAY([Date]-1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-1)+4);1;3))+5)/7;0))=52)&&MONTH([Date])=1;YEAR([Date])-1;IF((ROUNDDOWN(([Date]-DATE(year([Date]-WEEKDAY([Date]-1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-1)+4);1;3))+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])/3;0))-(ROUNDUP(MONTH(TODAY())/3;0));(ROUNDUP(MONTH([Date])/3;0))-(ROUNDUP(MONTH(TODAY())/3;0))+(4*(YEAR([Date])-YEAR(TODAY()))));»Semestre Actual» ; ROUNDUP(MONTH(TODAY())*2/12;0);»Desvio Semestre» ; IF(YEAR([Date])-YEAR(TODAY())=0;(ROUNDUP(INT(FORMAT([Date];»M»))*2/12;0))-(ROUNDUP(MONTH(TODAY())*2/12;0));(ROUNDUP(INT(FORMAT([Date];»M»))*2/12;0))-(ROUNDUP(MONTH(TODAY())*2/12;0))+(2*(YEAR([Date])-YEAR(TODAY())))))

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/2013 al 31/12/2021.

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

Os dejo un .txt con el código ya que si copiais y pegais directamente desde web da error por el tipo de comillas que usa wordpress.

dimfecha

 

Recommended Posts
Showing 21 comments
  • Manuel Martínez

    Buenos días Francisco

    Enhorabuena por el trabajo y la aportación que realizas a los que nos iniciamos en este mundo. Al crear la nueva tabla y copiar la fórmula de la tabla calendario me sale el siguiente error: Error de sintaxis durante el análisis: token no válido, Línea 5, Desplazamiento 21, ”.
    Me estoy iniciando con el Power Bi para poder sacar información relevante en la empresa que tengo y por mucho que reviso la fórmula no sé como puedo corregirla. Espero tu ayuda, gracias y saludos.

  • Francisco Mullor Cabrera

    Hola Manuel. Disculpa, no me saltó la notificación del comentario y por eso he tardado en contestarte.

    El problema es que el wordpress pone las comillas de diversas formas y al copiar y pegar dan error.

    Voy a subir a un archivo txt el código modificado y lo pongo en el post.

    Un saludo

  • Roberto

    no veo el descargable…

    • Francisco Mullor Cabrera

      Hola Roberto. En la última línea, justo antes de la imagen final, presionando sobre dimfecha se abre un archivo txt con el código correcto

  • Fernando Velasco Roldán

    Muy buenos días Francisco, hice uso del código que compartiste, me dio muy buenos resultados para lo que buscaba, solo tengo dos dudas, si uso ; (punto y coma) me indica error, lo cambie por las ,(coma) y todo funcionó de maravilla, considero que eso se debe a la versión de PwBi, y la otra es como puedo cambiar las fechas de inicio y fin, realice el cambio en la línea 3 ==>> CALENDAR ( DATE ( 2013, 1, 1 ), DATE ( 2023, 12, 31 ) ) pero el resultado no me cambia, si me pudieras apoyar con esas dudas, muchas gracias.

    • Francisco Mullor Cabrera

      Efectivamente Fernando, el cambio de «;» por «,» es por la versión de Power BI que utilices.En la versión inglesa, el separador de cada elemento de la fórmula es la «,» y en la versión en español es el «;»

      En cuanto al cambio de la fecha, con la que hay en el script salen 3287 filas y cambiando el 2021 por el 2023, 4017 filas mira el número de filas directamente en la tabla porque debe cambiarte seguro. Ordena de mayor a menor la fecha y seguro que observas el cambio.

  • JOSE ROBERTO TORRES LOPEZ

    Muchas gracias francisco pude descargar el TXT.

    Ya viendo la tabla me surgen unas dudas que espero sea el lugar en donde las pueda poner, si no fuera así una disculpa.

    Necesito agregar a una tabla calendario (esta u otra) varias columnas y no se como «empatar» las fechas me explico:

    Trabajo con un cliente que me envía información por semana pero no es semana calendario por ejemplo, la semana del cliente inicia los SABADOS y termina los VIERNES y la semana 01 de su año la ultima de enero o la primera de febrero según caiga en el año, por lo que la primer semana del año calendario para ellos es la semana 49 del año anterior.

    ejemplo para este 2019:

    ENERO
    Sem Cliente S S M T W T F

    49 01 02 03 04
    50 05 06 07 08 09 10 11
    51 12 13 14 15 16 17 18
    52 19 20 21 22 2 24 25
    53 26 27 28 28 30 31

    FEBRERO
    Sem Cliente S S M T W T F

    01 01
    02 02 03 04 05 06 07 08
    03 09 10 11 12 13 14 15
    04 16 17 18 19 20 21 22
    05 23 24 25 26 27 28

    Y así sucesivamente.

    entonces, necesito agregar las columnas de semana del cliente con el formato añosemana (201901, 201902) y por como me mandan las ventas semanales debo cerrar el mes en donde tenga mas días por ejemplo la semana en donde el dia 1 de febrero cierra la venta entonces se carga la semana 53 a enero. por supuesto que habrá meses de 5 semanas.

    Espero que me haya dado a entender o de lo contrario como te puedo contactar para enviarte unos ejemplos.

    Gracias

  • Agusti

    Hola buenos dias

    viendo el video que has realizado (enhorabuena!). tengo una duda.

    veo que usas la tabla que has creado con fechas con una tabla «calendario» + periodo.

    que sucederia si no hiciera esto? y usara directamente el calendario creado contra los datos?, es decir no usar ni dfecha ni periodo?

    Muchas gracias por tu labor!

    • Francisco Mullor Cabrera

      ¿Te refieres a usar sólo la dimensión fecha sin la dimensión período? Si es eso se puede prefectamente como con cualquier otra tabla de fechas, sin tener la opción de utilizar la segmentación dinámica que te proporciona la dimensión período.

      Si te refieres a utilizar la dimensión período directamente contra la tabla de hechos, también se puede ya porque se pueden establecer relaciones manytomany pero los cálculos de inteligencia temporal se basan en una tabla de fechas con fechas correlativas y únicas.

      No sé si es a esto en lo que te refieres en tu pregunta.

      Un saludo

  • Agusti

    me referia a usar solo la tabla calenario (dfecha) contra la tabla de hechos. e ir creando medidas con calculos.

    Muchas gracias

    • Francisco Mullor Cabrera

      Sí, por supuesto, eso no es problema, es el sistema habitual de utilización de una tabla de fechas. La inclusión de la tabla período, que es una idea que estableció Chris Webb, sirve para dinamizar nuestro calendario y nuestros informes, de manera que simplemente con la segmentación del período, podemos obtener un mismo informe desde diferentes puntos de vista temporales, el ejercicio actual, el trimestre actual, el mes actual, los últimos 30 días, etc. como muetsro en el vídeo.

  • Julio Isla

    Hola, me parece genial tu gran apoyo pero tengo el siguiente inconveniente, cuando coloco la expresión me sale el siguiente mensaje
    «La expresión hace referencia a varias columnas. No se pueden convertir varias columnas a un valor escalar.»
    copie el TXT pero aun no puedo solucionarlo.

    • Francisco Mullor Cabrera

      Hola Julio.
      ¿No estarás pegando el script en una medida? Tiene pinta de eso el error.
      Hay que pegarlo en nueva tabla.
      Un saludo

  • Rebeca

    Buenos días,
    en mi caso he creado la tabla tal y como comentas, ha ido perfecto, gracias, pero lo que quiero es que la semana aparezca como la jerarquía de fechas predefinida por Power BI, es decir, que pueda escoger entre Año, Trimestre, Mes, SEMANA y Día.

    Sería posible usando su tabla?

    Gracias

  • Alejandro

    Hola, una consulta, la fecha la tengo en formato de Estados Unidos, ejemplo: 10/31/2020, pero ocupo cambiar que sea primero el día y luego el mes 31/10/2020

  • Matias Zayas

    muy bueno! Gracias por compartir

  • Víctor Gutiérrez

    Gracias por compartir. Excelente aporte, solo me toco cambiar las comillas y reemplazar ; por ,

  • Alejandra Barrera

    Profesor!!!! Maestro!!! Mil gracias que post tan excelente y lleno de calidad, estoy muy interesada en sus cursos, gracias por tan genial aporte, mis respetos! Saludos desde Colombia.

  • FERNANDO R GIMENEZ

    Hola, queria consultar si se puede setear el comiezo del ejercicio fiscal el 1 de julio, en vez del 1 de enero. Gracias

  • Argenis Chaffardet

    Buenos días,

    Queda mejor formateado como dicen los italianos. Gracias por compartir.

    https://www.daxformatter.com/

    dFechas =
    VAR Fecha =
    CALENDAR ( DATE ( 2013; 1; 1 ); DATE ( 2021; 12; 31 ) )
    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] ) / 3; 0 );
    «NroTrimestre»; ROUNDUP ( MONTH ( [Date] ) / 3; 0 );
    «DiaSemana»; WEEKDAY ( [Date]; 2 );
    «Semana»; WEEKNUM ( [Date]; 2 );
    «Nombre Dia»; UPPER ( FORMAT ( [Date]; «DDDD» ) );
    «EjercicioTrimestre»;
    COMBINEVALUES (
    «-«;
    YEAR ( [Date] );
    «T»
    & ROUNDUP ( MONTH ( [Date] ) / 3; 0 )
    );
    «Nº Semana ISO»;
    ROUNDDOWN (
    (
    [Date]
    – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 )
    + WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 ) ) + 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 () ) / 3; 0 );
    «Semestre»;
    ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 12; 0 );
    «EjercicioMes»; COMBINEVALUES ( «-«; YEAR ( [Date] ); FORMAT ( [Date]; «MM» ) );
    «EjercicioISO»;
    IF (
    OR (
    (
    ROUNDDOWN (
    (
    [Date]
    – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 )
    + WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 ) ) + 5
    ) / 7;
    0
    )
    ) = 53;
    (
    ROUNDDOWN (
    (
    [Date]
    – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 )
    + WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 ) ) + 5
    ) / 7;
    0
    )
    ) = 52
    )
    && MONTH ( [Date] ) = 1;
    YEAR ( [Date] ) – 1;
    IF (
    (
    ROUNDDOWN (
    (
    [Date]
    – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 )
    + WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 ); 1; 3 ) ) + 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] ) / 3; 0 ) )
    – ( ROUNDUP ( MONTH ( TODAY () ) / 3; 0 ) );
    ( ROUNDUP ( MONTH ( [Date] ) / 3; 0 ) )
    – ( ROUNDUP ( MONTH ( TODAY () ) / 3; 0 ) )
    + (
    4
    * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
    )
    );
    "Semestre Actual";
    ROUNDUP ( MONTH ( TODAY () ) * 2 / 12; 0 );
    "Desvio Semestre";
    IF (
    YEAR ( [Date] ) – YEAR ( TODAY () ) = 0;
    (
    ROUNDUP ( INT ( FORMAT ( [Date]; "M" ) ) * 2 / 12; 0 )
    )
    – (
    ROUNDUP ( MONTH ( TODAY () ) * 2 / 12; 0 )
    );
    (
    ROUNDUP ( INT ( FORMAT ( [Date]; "M" ) ) * 2 / 12; 0 )
    )
    – (
    ROUNDUP ( MONTH ( TODAY () ) * 2 / 12; 0 )
    )
    + (
    2
    * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
    )
    )
    )

  • Argenis Chaffardet

    Buenos días,

    Algo que me faltó añadir en el comentario anterior es definir el idioma, los que siempre lo usamos en español pero el Power BI en ingles mejor definir en FORMAT el idioma, por ejemplo:

    «Mes», UPPER ( FORMAT ( [Date], «MMMM», «es» ) ),

    Saludos