Filtros de fecha relativa en Power BI / DAX / Power Query

  • Facebook
  • Twitter
  • LinkedIn

Hace algunos años (¡en marzo de 2013!), escribí un artículo en el Blog de Rob (PowerPivotPro) sobre cómo crear filtros de fecha relativos.

El título de esa publicación era “cómo mostrar siempre los datos de Ayer, Hoy o Mañana” con DAX en PowerPivot en Excel.

El propósito de la entrada era abordar el problema de la imposibilidad de hacer filtros de fecha dentro de PowerView y cómo / cuándo las funciones TODAY() y NOW() de DAX eran evaluadas para las Columnas Calculadas y Medidas.

Hasta el día de hoy, sigo usando una variación de ese método de filtros de fecha relativos en un reporte para que siempre se muestren cosas como:

  • Año en curso
  • Mes en curso
  • Semana en curso
  • Ventas de Ayer
  • Rendimiento en los últimos 7 días

Es una solución sencilla pero poderosa que mejora la Experiencia del Usuario. Un ejecutivo únicamente tendría que abrir un informe e inmediatamente vería los datos que desea ver. Sin necesidad de pasar por una segmentación/filtro para obtener los resultados que necesita.

Ya estamos en el 2019, y nunca he abordado nuevos enfoques, entonces, ¡aquí va mi método después de 6 años!

Método 1: Usar Funciones DAX

This is pretty much the same pattern that I created for that blog post over 6 years.

Este es básicamente el mismo patrón que cree para ese blog hace ya 6 años.

Aún es una opción bastante viable, pero antes que comiences, necesitas tener una Tabla Calendario. Puedes leer este patrón completo sobre cómo crear una con Power Query

En cuanto tengas lista una Tabla Calendario, puedes realizar una de las siguientes opciones:

  • Crear una Columna Calculada –se evalúa únicamente cuando la tabla tiene que ser evaluada
    • Una fórmula como =[Date Column] – TODAY()daría un número positivo para los días después de hoy, y un número negativo para los días previos a la fecha de hoy. Con esos valores puedes ya sea crear una relación con otra tabla y así decodificar el 0 como HOY, -1 para AYER y 1 para MAÑANA o hacer alguna clase de BINDING.
  • Crear una medida –se evalúa cada vez que tus necesidades visuales necesiten ser evaluadas nuevamente
    • Con un filtro como Calendar[Date]= TODAY() o usando TODAY() en conjunción con otras funciones de inteligencia de tiempo para evaluar el valor TODAY() y usarlo como desees en tu Filtrar Contexto

Mientras este es un abordaje aún válido, usar los siguientes 2 métodos es normalmente una mejor idea incluso si aún usas PowerPivot en Excel 2010.

Método 2: Usar el Filtro de Segmentación de Fechas Relativas en Power BI

Esto es bastante sencillo en Power BI, en donde únicamente tienes que arrastrar un campo de fecha y activar la visualización del “Filtro”:

Luego puedes transformar ese filtro de fecha a un Filtro de Fecha Relativo:

  • Facebook
  • Twitter
  • LinkedIn

Y, por último, haz los cambios que necesites ver reflejados en el funcionamiento de tu filtro de fecha relativo:

  • Facebook
  • Twitter
  • LinkedIn

Puedes leer la documentación oficial sobre este procedimiento aquí.

Esta es una solución fácil y rápida de implementar en caso quieras crear fechas relativas con Power BI.

Normalmente le recomiendo a la gente que haga la prueba si sus requerimientos no son complejos.

Método 3: Usar Power Query/M

Lo que los métodos previos hacen de forma predeterminada es averiguar la zona horaria local y partir desde allí. Esto puede ser una buena idea si trabajas localmente y la zona horaria es exactamente la misma que la fecha de los informes y datos que han sido almacenados.

pero…

  • ¿QUÉ SUCEDE SI tu máquina está en una zona horaria diferente?
  • ¿QUÉ SUCEDE SI tus datos están en una zona horaria distinta a la tuya?
  • ¿QUÉ SUCEDE SI el servicio Power BI es el que actualiza el archivo y no sabes la zona horaria del servidor?

Esos son MUCHOS elementos por manejar, y ninguno de los métodos anteriores puede manejar la zona horaria desde un punto de vista dinámico. Necesitarías saber cuál es la zona horaria para luego hacer un ajuste manual.

Aquí es donde entra la magia de Power Query. En Power Query puedes hacer maravillas cuando se trata de crear Tablas Calendario y probablemente ya lo has visto aquí.

El concepto en general es agregar una columna personalizada a tu Tabla Calendario usando la Fórmula siguiente:

Number.From (  Date.From(DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), #”TimeZone Offset” )) – [Column1])

He resaltado las 2 partes más importantes del código.

DateTimeZone.UtcNow() siempre calculará la fecha actual y la hora basada en la zona horaria UTC 0

DateTimeZone.SwitchZone hará un “switch”, o cambio. Cambiará la zona horaria a cualquiera que sea la correcta. En mi caso, estoy usando un Parámetro que llamo “Timezone Offset” y puede ser un valor numérico.

Aquí está el código completo para esa consulta:

let
Source = List.Dates( #date(2017,1,1), Number.From( #date(2025,12,31) – #date(2017,1,1) ), #duration(1,0,0,0)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Inserted Age” = Table.AddColumn(#”Converted to Table”, “DaysFromToday”, each Number.From (  Date.From(DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), #”TimeZone Offset” )) – [Column1], type  number))
in
#”Inserted Age”

No olvides que las Columnas Personalizadas serán evaluadas únicamente cuando actualices la consulta, entonces en caso necesites que sea calculada cada vez que hagas una consulta visual en tu informe final, deberás usar el método DAX con una medida. Aun así recomiendo que no lo hagas ya que estas funciones que mencioné están disponibles únicamente en Power Query.

Lo más bello del último método es que puedes averiguar la zona horaria y cambiar la forma en la que se comporta de manera muy sencilla utilizando únicamente la funcionalidad nativa.

Esta técnica también te puede ayudar a calcular cualquier cosa, siempre y cuando tenga que ver con fechas dinámicas.

¿Cuántos días han pasado desde X fecha? Incluso puedes usarlo para obtener los minutos y segundos si alguna vez lo necesitas.

También me he dado cuenta de que hay un nuevo artículo en el blog de PowerPivotPro que fue publicado hace algunas semanas sobre este tema, al igual de cómo manejar el horario de verano con esta técnica.

Me tomó 6 años hacer la secuela de mi publicación original, pero al fin está aquí.