Filtros de tiempo relativos en Power BI, YTD seleccionado un único mes y año

Dentro del mundo de la consultoría a veces los clientes piden cosas que a priori son conceptos sencillos de entender pero que, con las herramientas de las que disponemos, son complicadas de implementar. Uno de estos casos es el que nos ocupa hoy, se trata de una petición simple:
Quiero un panel de power BI en el que yo selecciono “un único mes” desde un slicer y se me muestren los datos desde principio del año hasta el mes seleccionado en ese slicer.

En un principio podemos pensar que esto debe ser sencillo de implementar, con los filtros de fecha relativos, seleccionamos un mes y un año y …
No… los filtros relativos te permiten indicar un periodo de tiempo relativo al día el que nos encontramos (o sea hoy). Un filtro relativo te muestra los datos de los “últimos 2 años” o de “la semana en curso”, pero siempre relativo al día de hoy, pero no nos permite seleccionar un momento del tiempo y relativizar dese ahí, o sea no podemos seleccionar febrero de 2015 y que desde ahí poder decirle que muestre datos de los dos últimos años.

Para poder implementar lo que el cliente nos está pidiendo en el ejemplo que nos ocupa, el modelo que usaremos contiene una tabla de hechos una dimensión fecha y una dimensión cliente:

La forma habitual de mostrar los datos de este modo en powerBI, sería haciendo que el usuario seleccione todos los meses que desea ver de un año previamente filtrado, esto quedaría algo así:

Como podéis ver, hemos seleccionado el cliente 3, el año 2017 y los meses de enero a junio, por lo que en la tabla se mostrarán exactamente esos datos. Pero nuestro cliente no quiere esto, quiere tener el mismo comportamiento, pero seleccionando únicamente el mes de corte, junio en este caso.

Esto se vería de este modo:

Pero como conseguimos este comportamiento. Una de las maneras que se nos ocurre es la de crear una tabla donde indicamos para cada mes seleccionado que meses pertenecen a su grupo, de modo que si selecciona marzo esta tabla lo relacionara con enero, febrero y consigo mismo.

Concretamente lo que necesito es que si selecciona enero se relacione con el mes 1, si selecciona febrero se relacione con los meses 1 y 2, para marzo los meses 1, 2 y el 3… y así sucesivamente. Pues lo que voy a necesitar es una tabla con esa relación. Algo así:

Esta tabla la cruzamos con la dimensión de fecha para conseguir una nelacion M:N con la tabla de hechos, llamaremos a la tabla “HastaFecha” y para ello utilizaremos esta query DAX:

HastaFecha = FILTER(CROSSJOIN('Dim_Fecha';GENERATESERIES(1;12;1));[Value] >= 'Dim_Fecha'[Mes])

Esta consulta dax cruza la dimensión de fecha, con una lista de números del 1 al 12 mediante un cross join y se queda unidamente con los miembros mayores al mes que se está cruzando.

Este mismo comportamiento se puede obtener con esta query de SQL (pero es mas engorroso y menos elegante):

with FilterDate as (
Select 12 as Mes, 12 as MesHijo union all

Select 11 as Mes, 11 as MesHijo union all
Select 11 as Mes, 12 as MesHijo union all

Select 10 as Mes, 10 as MesHijo union all
Select 10 as Mes, 11 as MesHijo union all
Select 10 as Mes, 12 as MesHijo union all

Select 9 as Mes, 9 as MesHijo union all
Select 9 as Mes, 10 as MesHijo union all
Select 9 as Mes, 11 as MesHijo union all
Select 9 as Mes, 12 as MesHijo union all

Select 8 as Mes, 8 as MesHijo union all
Select 8 as Mes, 9 as MesHijo union all
Select 8 as Mes, 10 as MesHijo union all
Select 8 as Mes, 11 as MesHijo union all
Select 8 as Mes, 12 as MesHijo union all

Select 7 as Mes, 7 as MesHijo union all
Select 7 as Mes, 8 as MesHijo union all
Select 7 as Mes, 9 as MesHijo union all
Select 7 as Mes, 10 as MesHijo union all
Select 7 as Mes, 11 as MesHijo union all
Select 7 as Mes, 12 as MesHijo union all

Select 6 as Mes, 6 as MesHijo union all
Select 6 as Mes, 7 as MesHijo union all
Select 6 as Mes, 8 as MesHijo union all
Select 6 as Mes, 9 as MesHijo union all
Select 6 as Mes, 10 as MesHijo union all
Select 6 as Mes, 11 as MesHijo union all
Select 6 as Mes, 12 as MesHijo union all

Select 5 as Mes, 5 as MesHijo union all
Select 5 as Mes, 6 as MesHijo union all
Select 5 as Mes, 7 as MesHijo union all
Select 5 as Mes, 8 as MesHijo union all
Select 5 as Mes, 9 as MesHijo union all
Select 5 as Mes, 10 as MesHijo union all
Select 5 as Mes, 11 as MesHijo union all
Select 5 as Mes, 12 as MesHijo union all

Select 4 as Mes, 4 as MesHijo union all
Select 4 as Mes, 5 as MesHijo union all
Select 4 as Mes, 6 as MesHijo union all
Select 4 as Mes, 7 as MesHijo union all
Select 4 as Mes, 8 as MesHijo union all
Select 4 as Mes, 9 as MesHijo union all
Select 4 as Mes, 10 as MesHijo union all
Select 4 as Mes, 11 as MesHijo union all
Select 4 as Mes, 12 as MesHijo union all

Select 3 as Mes, 3 as MesHijo union all
Select 3 as Mes, 4 as MesHijo union all
Select 3 as Mes, 5 as MesHijo union all
Select 3 as Mes, 6 as MesHijo union all
Select 3 as Mes, 7 as MesHijo union all
Select 3 as Mes, 8 as MesHijo union all
Select 3 as Mes, 9 as MesHijo union all
Select 3 as Mes, 10 as MesHijo union all
Select 3 as Mes, 11 as MesHijo union all
Select 3 as Mes, 12 as MesHijo union all

Select 2 as Mes, 2 as MesHijo union all
Select 2 as Mes, 3 as MesHijo union all
Select 2 as Mes, 4 as MesHijo union all
Select 2 as Mes, 5 as MesHijo union all
Select 2 as Mes, 6 as MesHijo union all
Select 2 as Mes, 7 as MesHijo union all
Select 2 as Mes, 8 as MesHijo union all
Select 2 as Mes, 9 as MesHijo union all
Select 2 as Mes, 10 as MesHijo union all
Select 2 as Mes, 11 as MesHijo union all
Select 2 as Mes, 12 as MesHijo union all

Select 1 as Mes, 1 as MesHijo union all
Select 1 as Mes, 2 as MesHijo union all
Select 1 as Mes, 3 as MesHijo union all
Select 1 as Mes, 4 as MesHijo union all
Select 1 as Mes, 5 as MesHijo union all
Select 1 as Mes, 6 as MesHijo union all
Select 1 as Mes, 7 as MesHijo union all
Select 1 as Mes, 8 as MesHijo union all
Select 1 as Mes, 9 as MesHijo union all
Select 1 as Mes, 10 as MesHijo union all
Select 1 as Mes, 11 as MesHijo union all
Select 1 as Mes, 12 as MesHijo)

select 
fe.*, fd.MesHijo 
from 
dw_DWH.dim.Fecha fe 
inner join filterdate fd 
on fe.Mes = fd.Mes

una vez tenemos la tabla, la añadimos al modelo y creamos la relacion N:M marcando la propiedad de las relaciones entre las tablas como “Cross filter direction = Both”, quedando así:

Hemos añadido a la tabla una columna calculada para el nombre del mes “Hasta Nombre mes” con el siguiente código:

Hasta Nombre Mes = 
SWITCH(HastaFecha[Hasta Mes];
1;"Enero";
2;"Febrero";
3;"Marzo";
4;"Abril";
5;"Mayo";
6;"Junio";
7;"Julio";
8;"Agosto";
9;"Septiembre";
10;"Octubre";
11;"Noviembre";
12;"Diciembre")

y ocultamos el resto de miembros de la tabla dejando solo visibles “Hasta Nombre Mes” y “Hasta Mes” que son los atributos que utilizaremos para el filtro en cuestión.

Ahora nuestro panel ya muestra el tramo de meses desde inicio de año, seleccionando únicamente el mes de corte.

Si tenéis cualquier duda no os cortéis y dejad un comentario 🙂

Saludos.