Miguel Escobar Publicada abril 2, 2019
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:
Y, por último, haz los cambios que necesites ver reflejados en el funcionamiento de tu filtro de fecha relativo:
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í.
Hola, Gracias por tus comentarios, ayudan mucho.
Tengo la siguiente duda en una consulta, tengo la sigiente sentencia
«#»Filas filtradas» = Table.SelectRows(#»Encabezados promovidos», let latest = List.Max(#»Encabezados promovidos»[Fecha importacion]) in each [Fecha importacion] = latest)», pero quiero que ponga la semana anterior a la que está seleccionando.
Gracias
Hola!
Creo que estás utilizando power Query y no dax cómo se plantea en el artículo de arriba. Te recomiendo entrar a nuestro foro para plantear tu escenario con lujo de detalles y así obtener ayuda de nuestra comunidad:
https://foro.poweredsolutions.co/
Buenas
Buenas necesito almacenar el último día del mes filtrado de CALENDAR en otra tabla para contar días pero al final me almacena el 31-12 si marco de enero a marzo en MEDIDA lo saca bien pero al llevarlo a otra columna pone el último día del año.
Las tablas no están relacionadas, pero he probado relacionándolas y obtengo el mismo resultado.
Hola Ana!
No creo que haya captado correctamente lo que estás requiriendo. Mi sugerencia es que publiques tu consulta con lujo de detalles, archivos de ejemplo e inclusive capturas de pantalla de lo que estás buscando en el foro oficial de Power BI en español:
https://community.powerbi.com/t5/Translated-Spanish-Desktop/bd-p/pbi_spanish_desktop
Saludos!
Hola Miguel, es muy bueno el foro que llevas adelante. Tengo una duda hay alguna manera de tener dos controles (segmentadores) de fecha en el mismo informe? o sea necesito mostrar ventas de unos pocos días, y por otro lado promedios de toneladas y ventas de los últimos 6 meses. O sea tengo entendido que al haber dos controles de fecha en el mismo informe, prioriza la que tenga menor longitud. Es posible esto?. Muchas gracias.
Es algo que está fuera del alcance de este artículo, pero es posible. Todo depende de cómo tengas tu modelo de datos pues la solución sería diferente de si ambos campos de fecha están en la misma tabla, si deseas utilizar una o múltiples tablas de fechas o si deseas manejar esto a través de una fórmula DAX.
Te recomiendo publicar tu consulta con lujo de detalles en el foro oficial de Power BI (enlace abajo):
https://community.powerbi.com/t5/Translated-Spanish-Desktop/bd-p/pbi_spanish_desktop
Saludos!
Muchas gracias Miguel, sabes que intento loguearme o registrarme en realidad en el foro de la comunidad y no me deja, hace meses me pasa. Entonces lo que necesito hacer es posible?
Creo que es posible, pero todo depende de cómo tengas tu modelo de datos y el tipo de interacción que desees que tenga tu reporte a nivel de visuales y el contexto de filtro que aplica para cada uno. No tengo un artículo que hable de ello y lamentablemente tampoco he encontrado otro en español que hable de ello. Por eso es que mi recomendación es que publiques tu consulta con lujo de detalles en una plataforma como el foro oficial de Power BI donde podrías subir archivos, imágenes y explicar con mucho más lujo de detalles todo tu escenario.
Lamentablemente esta sección de comentarios no es la mejor para poder solucionar brindarte la ayuda que buscas y tampoco tiene todas la bondades de un foro completo como el que tiene Microsoft. Es por ello que trato de brindar este tipo de apoyo a través de dichos foros los cuales tratar de brindar apoyo a toda la comunidad y así muchas personas se beneficiarían al conocer tu caso y poder conocer cómo resolverlo de manera pública y centralizada (En el foro de MSFT).
¿Has probado contactando al soporte de Power BI para que te ayuden con el tema de la cuenta? Acá te paso la página oficial de soporte:
https://powerbi.microsoft.com/support/
Hola necesito mostrar la variación del entre los últimos meses , cada vez que se actualice la data
Hola Sergio!
Creo que tu mejor sería la de crear una medida en DAX para crear la variación y definir de manera muy concreta cómo deseas que todo el mecanismo funciona, desde el tema del refrescado (su frecuencia), hasta dónde y cómo almacenarías las fechas y tiempos cuando se refrescó tu conjunto de datos y luego cómo planeas que la medida DAX utilice ese valor de fecha/tiempo.
Realmente no tengo la información completa de lo que deseas lograr o cómo realmente necesitas que funcione, pero el artículo que he escrito te puede ayudar con el tema de las fechas relativas.
Saludos y suerte!
Que tal Miguel muy buena tarde,
Muy buen artículo, muchas gracias por tu aporte.
Una duda, la fecha relativa (meses calendario para esta ocasión) me ha dado muchos problemas. En una matriz en la cual tengo diversos costos de diversas plantas, 3 en total quisiera poder calcular el promedio movil de los ultimos 6 meses de cada una y ese promedio movil evaluarlo contra el dato el ultimo mes y así saber si va mejor o peor que el promedio, no he logrado una fórmula que me arroje el resultado correcto. ¿Alguna sugerencia?
Muchas gracias de antemano
Saludos
Pedro
creo que este te podría ayudar, pero está enfocado en DAX que creo sería la mejor opción para tu matriz:
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
Espero te sea de ayuda.
Saludos!
Tengo una tabla de historico de personal, pero quiero que al cargarse solo muestre los vigentes al dia actual.
He conseguido con el programa poner esto:
= Table.SelectRows(R_V_DR_DATOS_EMP1, each [F_INICIO] <= #datetime(2023, 9, 21, 0, 0, 0) and [F_FIN] >= #datetime(2023, 9, 21, 0, 0, 0) or [F_FIN] = null)
porque he marcado el dia de hoy, pero como puedo automatizar que esto se actualize cada dia?
Gracias
Podrías utilizar esta función para reemplazar la fecha estática que utilizas en el Table.SelectRows: https://learn.microsoft.com/en-us/powerquery-m/datetime-localnow