Comparar periodos NO paralelos en Power BI

Power BI proporciona fórmulas para comparar periodos paralelos fácilmente. Pero supongamos un hotel que desea comparar resultados de la campaña de Pascua actual, fiesta móvil, con la del año pasado, o de ocupación durante la celebración de una feria comercial con otra… Son muchos los casos en los cuales será necesario ofrecer al usuario la funcionalidad de comparar periodos de forma flexible.

Planteamos el caso

Nuestro usuario requiere comparar periodos temporales de forma independiente, más gráficamente, dos filtros (slicers) de fecha, uno para el periodo actual y otro para el comparado.

Como ejemplo vamos a considerar una tabla de hechos que contiene datos de ocupación de una pequeña cadena de hoteles, con cantidad e importe de los ingresos totales por hotel y día. Están en una archivo Excel, que cargaremos normalmente en Power BI y opcionalmente aplicaremos formatos a los importes y la fecha:

Preparar la tabla de fecha

Crearemos una tabla de fechas (en este artículo puedes ver cómo crear una tabla de fechas fácilmente) y la relacionaremos con la tabla de hechos.

Pretendemos lograr algo similar a esto:

“Copiar” una tabla con ALL

En este momento, el que tenga el filtro más restringido es el que condiciona los resultados mostrados. Para que ambos Slicers funcionen de modo independiente deben estar referidos a dos dimensiones diferente, no a la misma, por lo que necesitamos otra tabla de fechas, que podemos crear del mismo modo que la primera, o como necesitamos una copia de la creada anteriormente, podemos recurrir a la función DAX ALL:

FechasCopia = all(Fechas)

Usar este método permite hacer cambios en la primera tabla creada y asegurarnos de que los cambios se transmiten a la otra, lo que reduce el eventual mantenimiento del modelo.

Finalmente relacionamos la nueva tabla en el modelo. Pero si adaptamos los slicers anteriores y creamos una tabla observaremos que sigue sin funcionar los resultados según lo esperado, y que un filtro restringe los resultados del otro y sólo salen las fechas entre las cuales hay intersección de los filtros:

Relaciones activas y no activas

Eso es por que ambas relaciones están activas, así que vamos a desactivar la relación de la segunda tabla de fechas (quita el check donde se indica en la imagen):

Ahora, los cambios en el filtro FechasCopia no afectan al importe ni al rango Fechas, que es el “dominante” y que, por tanto, determinará los principales valores a mostrar.

Crear la medida DAX (USERELATIONSHIP)

Para el importe a comparar vamos a crear una nueva Measure que será la suma del Importe, pero para el periodo definido en FechasCopia. Para poder operar con relaciones no activas podemos recurrir a la función USERELATIONSHIP, la cual ya tratamos en el artículo Relaciones entre tablas en powerbi y funciones para manejarlas de este blog.

Usaremos un CALCULATE que sumará todos los Importes del periodo “activo” (lo que especificamos con ALL) pero con USERELATIONSHIP concretaremos la relación a utilizar para aplicar el filtro:

ImporteCompara = 
CALCULATE (
sum(VentaDiariaHoteles[Importe]);
ALL ( Fechas );
USERELATIONSHIP ( VentaDiariaHoteles[Fecha]; FechasCopia[Date])
)

Observaremos que devuelve la suma de Importe en cualquier caso, y que no funcionaría como serie temporal (ni tampoco tendría demasiado sentido usarlo con esta finalidad). Pero si mostramos, por ejemplo, la comparativa de los dos hoteles o el total, la comparativa cobra todo el sentido:

Optimizar un modelo con múltiples tablas a relacionar

En este ejemplo tan simple podemos relacionar del modo indicado. Pero en un modelo con gran cantidad de tablas, añadir una nueva tabla que hay que relacionar de nuevo, puede complicar el mantenimiento significativamente. Una forma de evitarlo es relacionar las dos tablas de fecha directamente y eliminar (o no crear) el resto de relaciones.

El tipo de relación debe ser 1:1.

ImporteCompara2 = 
CALCULATE (
sum(VentaDiariaHoteles[Importe]);
ALL ( Fechas );
USERELATIONSHIP ( Fechas[Date]; FechasCopia[Date])
)

Tan solo hemos especificado otro campo-tabla a usar en la relación (Fechas[Date]). Y el resultado es el mismo:

NOTA: En el ejemplo, como demostración he dejado ambas relaciones, pero en la práctica necesitamos sólo la nueva, por lo que la anterior la podríamos eliminar (y también la medida asociada a esta relación).

(ANEXO) Homogenizar los datos a comparar

En este post podrás ver cómo homogenizar valores para poderlos hacer comparables (por ejemplo, media diaria de cada uno de los periodos comparados) en caso de que fuese necesario.

Conclusiones

Si lo deseas puedes descargar los archivos utilizados en este post (XLS+PBIX):

Compara Diferentes Periodos (XLS+PBIX)

NOTA: Si deseas ver una alternativa a comparaciones no paralelas te puede interesar este post: https://www.biti.es/comparar-periodos-no-paralelos-sin-userelationship/

Te puede interesar...

32 comentarios en «Comparar periodos NO paralelos en Power BI»

  1. Hola Santiago,

    Gracias por tu aporte, me sirvió mucho. quisiera saber solo si es que se puede cambiar los nombres “Importe” e “ImporteCompara” de la tabla y que aparezca la fecha seleccionada tal cual.

    Gracias, me ayudaría un monton.

    1. Hola Stephanie, me temo que no puedes cambiar dinámicamente el nombre de una medida. Sí que puedes crear medidas que muestren texto y mostrarlas como título y/o subtitulo del visual, pero de la columna no lo veo.

  2. Hola Santiago,
    Saludos desde Ecuador, soy nuevo en Power BI Y llegué a tu web ya que en mi trabajo trabajo necesitan hacer comparaciones del P&G de acuerdo a dos periodos seleccionados. Seguí tu ejemplo y creé las dos tablas de fechas (Calendario): PeriodoA y PeriodoB y las uní con mi tabla “Data” la cual contiene la información que requiero, luego desactivé las relaciones y cree las medidas para poder visualizar mi valor de ventas, sin embargo se me repite los valores en ambas medidas. Las medidas son las siguientes:

    VentasA = CALCULATE([Ventas], ALL(PeriodoA),USERELATIONSHIP(PeriodoA[Date],Data[Fecha]))
    VentasB = CALCULATE([Ventas],ALL(PeriodoA),USERELATIONSHIP(PeriodoB[Date], Data[Fecha]))

    Cabe mencionar que en mi modelo existe otra tabla Calendario que es la que se utiliza para los filtros de los informes y para hacer cálculos.

    De antemano muchas gracias por la ayuda.

    Saludos.

    1. Hola Eduardo, seguramente no necesitas usar tablas relacionadas para este caso. Puedes usar estas tablas de fecha para sacar el número de días de diferencia con la fecha elegida en “calendario” con datediff (algo así
      Dias Atras Pickup = -datediff(max(FechasPk[Fecha Pickup]),MAX(‘Fechas Reserva'[Fecha Reserva]),DAY)
      Y usar esta diferencia en días para hacer cálculos basado en esta fecha:
      ADR Pick = CALCULATE([ADR], DATEADD(‘Fechas Reserva'[Fecha Reserva],[Dias Atras Pickup],DAY))
      Esto te permite tener una fecha que manda, la del calendario y usar las otras para calcular de forma totalmente independiente el o los periodos a comparar con el seleccionado.
      Un saludo

      1. Hola Santiago,
        Podrias apoyarme con una cuestión en una tabla de ventas estoy mostrando el último dato registrado para cada región usando las función LASTNONBLANK pero ahora intento comparar lo último registrado contra su paralelo de año anterior, he intentado con las fórmulas SAMEPERIOD, PARALLELPERIOD Y DATEADD pero me enfrento al mismo problema todas esas fórmulas se direccionan al calendario por lo que a menos que yo haga una seleción especifica de la fecha que pretendo ver, no me dan el dato correcto, sabes de alguna fórmula que pueda comparar el año anterior basado en la condición lastnoblank
        Muchas gracias!

        1. Hola Edith, a falta de más información, y si lo he entendido bien, te sugiero que metas la fecha obtenida con LASTNOTBLANK en una variable y a partir de la misma, con DATEADD -1 año, obtengas la ultima fecha del periodo anterior.
          Con estos datos ya puedes obtener valores de otros períodos.
          Algo así (pseudo-código):
          medida=
          var ultima=lastnotblank (o puedes usar LASTDATE)
          var paralela_a_ultima=DATEADD(ultima,-1,year)
          return
          calculate(filter(tabla, fecha

  3. Hola Santiago como estas? antes que nada, agradecerte por todos tus articulos, me sirvieron de mucha utlidad.
    Me encuentro con el siguiente desafio. Pude resolver como hacer para comparar tres periodos personalizados, el tema es que no se como hacer para que el slicer filtren los otros slicer, ya que son dependientes.
    Desde y Hasta Periodo 1: SALDO ACUMULADO con grafico
    Desde y Hasta Periodo 2: SALDO DETALLADO (TABLA MATRIZ) usualmente semana actual
    Desde y Hasta Periodo 3: SALDO A VENCER (grafico por semanas)
    Funciona bien todo, el tema es que la fecha final del periodo 1, deberia automaticamente ser igual a la fecha de inicio del periodo 2, y el incio del periodo 3 deberia ser igual al fin de periodo 2.

    Gracias,
    Saludos
    Damián.-

    1. Hola Damián, si lo he entendido bien, no se me ocurre una solución fácil de explicar. Sin posibilidad de profundizar demasiado la idea iría en la línea de la solución de este post Pareto dinámico, que usa un slicer para crear franjas desde-hasta, en las que el final de una sería el inicio de otra… Sólo faltaría añadir un filtro desde-hasta superior que permitiese identificar los límites inferior y superior de otro filtro en el que a su vez se podría definir el desde hasta, y con funciones dax min, max, datesbetween filtrar valores. Espero haberte aportado alguna idea. Saludos

  4. Buenas tardes ,
    Gracias la information me es de gran ayuda. Necesito un favor tengo tres tablas en las cuales todas tienen fechas de reportes diferentes, en una tabla me dice la cantidad de ordenes semanal en otra fecha de presupueto y la ultima fecha de diseño… lo que quiere decir que si en esa columna hay una fecha ya hay una orden, presupueto y diseño.
    Que necesito saber la cantidad de viviendas que hay por fecha? y que me muestre en total por semana.
    Gracias

    1. Hola Jenny, del mismo modo que puedes tener varias tablas de fecha relacionadas con una tabla de hechos, como en el caso del post, puedes tener una tabla de fechas relacionada con varias tablas de hechos (opcion 1). O cada tabla de hechos tener su propia tabla de fechas (opción 2). Dependerá de cómo quieras trabajar:
      2 opciones para un mismo planteamiento
      Respecto a semanas, a la tabla fechas puedes añadirle columna con función:
      WEEKNUM(, )
      Aquí más detalles de la misma. Saludos

  5. Buenas tardes,

    Enhorabuena por el post y la Web en general, he aprendido bastante leyéndole 🙂

    Le escribo porque agradecería muchísimo que me diese un poco de luz: estoy intentado calcular la diferencia entre dos valores de un mismo campo, para periodos de tiempo diferentes.

    Supongamos que en diciembre de 2019 vendo 10 barras de pan, y en enero de 2020 15. Simplemente necesito saber cómo puedo realizar la resta de esa misma variables, para dos periodos de tiempo diferentes, en valores relativos. ¿Es posible?

    Muchísimas gracias y que esté muy bien,

    1. Hola y muchas gracias por leerme, Iss. Es posible hacerlo como medida dax. En principio, si lo he entendido bien, ¿lo que requieres es sacar el valor paralelo (x dias, meses, años atrás) al periodo seleccionado?
      Si es así, puedes solucionarlo con Dateadd. Este ejemplo te saca el valor del perido parallelo de un año atrás:

      Importe N-1 DateAdd =CALCULATE(SUM(VentaDiariaHoteles[Importe]); DATEADD(Fechas[Date];-1;year))

      Pero cambiando el último parámetro puedes espeficar day, month… El siguiente post lo explica:

      https://www.biti.es/dateadd-sameperiodlastyear-parallelperiod-diferencias/

      Siguiendo con esta solución , puedes ver este post:

      https://www.biti.es/comparar-periodos-no-paralelos-sin-userelationship/

      … que soluciona la posibilidad de cambiar el número de periodos con un simple slider, lo que da mayores posibilidades y dinamismo al análisis.

      Espero haber sido de ayuda. Saludos

  6. Buenos días Santiago,

    En el caso del sector del turismo, nosotros solemos comparar las reservas creadas en un periodo, con fecha de entrada en una fecha determinada vs las reservas creadas en otro periodo, con otra fecha de entrada. Te pongo un ejemplo:

    Reservas creadas hasta 14/05/2019 con entrada en julio 2019 vs reservas creadas hasta 14/05/2018 con entrada en julio 2018. De ahí podemos establecer correcciones a futuro.

    Muchas gracias por tu ayuda.

    Alex

    1. Ahí van algunas ideas.
      Necesitarás tener dos columnas de fecha (fecha reserva y fecha entrada) cada una relacionada con su tabla de fechas (igual ni siquiera esto es necesario). El primer filtro de fecha filtra cuándo se hace la reserva, y el segundo la entrada. La siguiente imagen muestra el caso. Ejemplo reservas powerbi
      El problema es que de este modo restringes el año anterior y con fórmulas parallelperiod, etc, aunque utilices ALL, no saldrán valores.
      Añadiría una columna calculada que fuese la fecha actual-365:
      fecha Reserva N-1 = Reservas[fecha Reserva]-365
      Duplicaría la tabla reservas y relacionaría esta nueva columna con de la fecha de reserva de la tabla duplicada:
      Relaciones con tabla copiada
      El informe anterior, tras estos cambios permitiría sacar las reservas con entrada en periodos filtrados y lo crorrespondiente a reservas efectuadas durante el mismo periodo de un año atrás:
      Informe tras añadir reservas copia
      Espero que te sirva como punto de partida.

  7. Buenos días Santiago, se nota su amplio conocimiento. Tengo un pequeño problema con Sameperiodlastyear. Resulta que tengo un estado de resultados, donde el periodo anterior 2018 no me salen si el año actual 2019 no tiene valores para esa cuenta Ejm:
    Si el año pasado 2018 tenía útiles de oficina (cod 6023) x B/.50.00 y este año 2019 no tengo valor en esa cuenta, no me sale valor en el año 2018 = B/.0.00

    Aprecio mucho tu ayuda

    1. Hola Alex,

      No es problema de la función SamePeriodlastyear, si muestras en una tabla la medida actual y la comparada, si cualquiera de las dos tiene valor se mostrará la columna con la cuenta.

      Por otro lado hay varias opciones de mostrar valores “blank”.

      1. En un visual, por ejemplo tipo tabla, en el valor correspondiente a cuenta , botón derecho del raton, Activar la opción “Show ítems with not data”. A veces esta opción no fucniona adecuadamente y es necesario recurrir a medida dax…

      2. … por ejemplo, la siguiente medida mostraría una categoría, tenga o no valores para el periodo filtrado:

      Importe Con Blanks = IF( ISBLANK( SUM(VentaDiariaHoteles[Importe]) )
      ; 0
      ; SUM(VentaDiariaHoteles[Importe])
      )

  8. Muy buenas Santiago, abuso de nuevo de tu conocimiento.

    Tengo una tabla con x registros y en esos registros hay un campo fecha alta, otro fecha baja y uno que es importe. Lo que necesito hacer es que para cada mes del periodo que yo defina me calcule la suma de importe de aquellos registros que cumplan mes de la gráfica >=fecha alta y mes de la gráfica <=fecha baja o fecha baja sea nula.

    Mil gracias por tu ayuda.

    1. Más detalles:
      Para que puedas ubicarte te comento: En esa tabla tengo una fecha de alta, una de baja y un importe. Para que puedas ubicarte mejor imagina que cada registro se corresponde con un servicio que ha podido estar facturando durante x tiempo (desde que se da de alta hasta que se dá de baja. Lo que pretendo es que en una gráfica yo pueda determinar el tiempo que quiero analizar, por ejemplo desde Agosto de 2018 a Enero de 2019 y que me muestre por cada mes la suma de registros que cumplen esa condición, es decir, esos registros que tienen fecha de alta inferior o igual al mes analizado y fecha de baja inexistente o mayor al mes analizado.

      Respuesta:

      Hola Miguel, gracias por la aclaración.

      Entiendo que lo que necesitas es la suma de lo facturado entre dos fechas, sin más. Con una tabla con dos columnas, fecha cobro e importe, solucionas el caso (si no hay algo que esté pasando por alto). Si es posible, a veces es mejor cambiar el punto de vista. Si consigues la tabla en este formato, que posiblemente sea el original, simplificas el problema y podrás acceder a mayor capacidad de análisis.

      Por otro lado, por ejemplo, podrías tener el número de clientes activos por mes, si en esa misma tabla añades el IDCLiente, haciendo un simple distinct count, lo tendrías (eso en caso de que un cliente pueda tener varias cuotas en un mes, y si las cuotas son únicas, no necesitas ni IDcliente, solo un count de líneas).

      Saludos!

  9. Muy buenas Santiago, en primer lugar felicitarte por la web y el artículo. He tratado de seguir los pasos y algo debo estar haciendo mal, aunque no es el mismo caso.

    Tengo una tabla con los campos id_socio, Fecha_solicitud y Fecha_baja. Lo que yo pretendo es sacar en un gráfico ordenado por meses y años nº de socios por mes que piden alta (Fecha_solicitud) y los que piden baja (Fecha_baja). Para tener el nº he creado dos columnas que hacen el cálculo en modo binario (0 si no pide baja, 1 si la pide). A pesar de seguir tus pasos o bien me arroja la intersección sin tener en cuenta la fecha de la solicitud de baja o bien me las muestra todas.

    ¿Se te ocurre qué puedo estar haciendo mal?

    Muchas gracias.

    1. Hola Miguel, muchas gracias!
      Para el caso que explicas me temo que este artículo no es el indicado. Te sugiero que leas el siguiente post, pues ilustra un caso más en línea con el que describes:
      Relaciones entre tablas y funciones para manejarlas
      Si no lo he entendido mal, con una pocas adaptaciones (por ejemplo usar count en lugar de sum) creo que podrás resolverlo fácilmente.
      Espero haber sido de ayuda. Saludos!

      1. Santiago, solo decirte que en efecto, esa es la clave. Utilizando userelationship he conseguido activar la relación con el segundo campo de fecha y que así no marque la intersección.

        Muchas gracias de nuevo.

  10. Hola,

    Muy interesante el post.

    ¿Cómo se podría realizar la diferencia entre ambos periodos? ¿Y es posible solo comparar y calcular la diferencia entre fecha1 y fecha 2?

    1. Hola Fran,
      bastaría con crear una medida que restara el actual y el comparado para obtener la diferencia en términos absolutos y, si se requiere, crear otra que lo haga en términos porcentuales también es trivial.
      Quizá la respuesta cobre más sentido si lees este post:

      https://www.biti.es/hacer-datos-comparables-con-dax-comparar-periodos-no-paralelos/

      en el que se habla de homogenizar para hacer las medidas comparables: No tiene sentido comparar un periodo de 10 dias con otro de 40… Convirtiendo a media diaria, por ejemplo, podríamos ver y analizar las diferencias adecuadamente. De eso va el post. Saludos

  11. Hola, podrías ayudarme?

    Quiere replicar esta consulta en Power Bi, dicho conculta fue generada en report builder

    =IIf((Fields!Inicio.Value>=Parameters!Inicio_Periodo.Value) and (Fields!Inicio.Value=Parameters!Inicio_Periodo.Value) and (Fields!Modified.Value<=Parameters!Fin_Periodo.Value),
    IIf(Fields!Inicio.Value<Parameters!Inicio_Periodo.Value,"Aplicar","Pendientes"),"Pasadas"))

    Necesito usar dos párametros de entrada
    los cuales son Inicio_Periodo y Fin_Periodo

    Los campos que tengo en mi tabla son Inicio y Modified

    Espero me puedas ayudar

    1. Hola Carlos,

      No me das muchos detalles pero intentaré darte respuesta.
      A falta de más información podría sugerirte que creases dos listas fecha no relacionadas para los parámetros de entrada (Inicio_Periodo y Fin_Periodo)
      Estas dos listas se meten en sliders y a través de la función max podrías conocer el valor seleccionado en las mismas y usarlo en la medida, que sería algo similar a la que remites.
      El siguiente pantallazo puede servir para ejemplo:

      Ejemplo Slider fechas y función MAX

  12. Buenos días

    Muchas gracias por la ayuda, quería consultarle como puedo hacer una resta del mes anterior contra el mes en curso, esto con la finalidad de ver cuánto hemos se ha crecido en ventas o decrecido, soy nuevo en power Bi y aun me cuesta un poco quedo atento.

    1. Hola Diego, para calcular el valor del mes anterior hay varias alternativas.
      Por ejemplo DATEADD:
      MesAnterior = CALCULATE(SUM(‘Table'[Amount]),DATEADD(‘Table'[Date],-1,MONTH))
      Eventualmente podrías sustituir DATEADD por PARALLELPERIOD para el segundo parámetro:
      MesAnterior = CALCULATE(SUM(‘Table'[Amount]), PARALLELPERIOD(‘Table'[Date],-1, month))
      En principio, cualquiera de la soluciones es válida y la diferencia entre una y otra es que PARALLELPERIOD sólo permite intervalos anuales, trimestres o meses, mientras que DATEADD te permitiría hacer lo mismo pero también hasta nivel Dias.
      También notar que la segunda devuelve periodos completos, o sea, si hay un filtro fecha seleccionado del día 5 al 10 de agosto, DATEADD hará una comparación con periodo de 5 al 10 de julio, que es lo deseable, mientras que PARALLELPERIOD compararía con el mes de julio completo.

  13. Muy bueno el post pero para mejorarlo ¿Cómo podría homogenizar la comparación? Por ejemplo, si un periodo tiene 10 dias y el comparado 15, no son comparables ¿Cómo haría para convertirse a una media diaria?. Michas gracias y enhorabuena por su blog!

Responder a jenny Cancelar la respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *