Cualquier proyecto de Business Intelligence suele requerir análisis temporal de los datos: Por años, trimestres, meses, semanas o días. DAX incluye funciones de Time Intelligence que facilitan de modo nativo los cálculos que usan fechas o hacer comparaciones interperiodales fácilmente. También, la funcionalidad Questions&Answers de Power BI utiliza T.I.

Dentro del entorno PowerBI o PowerPivot (o PowerQuery) para el correcto funcionamiento de estas funcionalidades T.I. se requiere disponer, al menos, de una tabla de fechas, y podemos obtenerla de varias maneras, cada una con sus pro-contras.

Dentro de un mismo proyecto podemos tener todas las tablas de fecha que podamos necesitar pero si podemos utilizar el mínimo posible, incluso si es posible solo una, mejor, por simplicidad del modelo, de relaciones, de filtros en reports y cálculos DAX. Esto lo determinará, por supuesto, las necesidades del proyecto a desarrollar.

¿Qué alternativas tenemos?

Podemos partir de una columna de fecha de una tabla de hechos en nuestro proyecto pero hay que tener en cuenta un requisito para el correcto funcionamiento de T.I.:

No puede haber saltos entre las fechas, deben existir todos los días secuencialmente en esta columna, si no T.I. fallará.

Si estamos con un diario de contabilidad no es fácil que tengamos asientos registrados todos los días y quizá no sea aconsejable pensar que con seguridad sí que va a ser así. Aunque limitada, no deja de ser una opción y en un modelo rápido (como alguno de los ejemplos que puedes ver en este blog) puede ser operativo.

Hay quien tiene sus tablas de fechas con sus campos año, mes, trimestres, etc… precalculados,  y las agrega a sus proyectos, las relaciona con el resto de tablas y a analizar. Como ventaja, también permite relacionar con diferentes tablas del modelo de datos que contengan cada una su campo de fecha, o incluso con varios campos fecha dentro de una misma tabla, aunque esto requiere un conocimiento previo de cómo funcionan las relaciones en PowerBI. Es otra opción. Pero requiere mantener una tabla más, añadirla a nuestro modelo de datos, etc… También se puede justificar si accedemos a un Datawarehouse cuyos datos se analizan en otros entornos diferentes a Power BI.

En ambos casos es importante especificar el campo correspondiente, si es que el Query Editor no lo ha detectado, como tipo Datetime.

También podemos recurrir a funciones DAX para crear una tabla de fechas automáticamente. Lo vemos a continuación.

Creación automática usando la función CALENDAR

Comenzamos creando una nueva tabla (“New Table”) y en el cuadro de funciones especificaremos una expresión DAX que llenará esta tabla.

Usamos la función CALENDAR que sólo necesita dos parámetros: fecha desde y fecha hasta:

Tabla= CALENDAR ("01/01/2015";"31/12/2016")

El formato fecha tendrá en cuenta la configuración regional y debe ir entrecomillado. Si quieres obviar inconvenientes en este sentido puede usarse la función DATE:

Tabla= CALENDAR (DATE (2015; 1; 1), DATE (2016; 12; 31))

Como precaución, asegúrate de que la fecha hasta es superior a desde.

Esta simple instrucción agrega la columna “Date” a la nueva tabla y la llena con todos los días comprendidos entre el rango definido, sin huecos, uno de los requisitos a cumplir para poder operar con Time Intelligence.

“New Table” con funcion Calendar en Power BI

Y como tabla de pleno derecho que es, vamos a poderla relacionar con otras tablas del proyecto, añadir columnas, cambiar nombres, formatearla…

En estas circunstancias nos vemos obligados a cambiar manualmente las fechas, que en determinados proyectos puede ser suficiente. Pero podemos hacer que el rango desde-hasta sea variable y olvidarnos de este mantenimiento manual. Para ello podemos basarnos en la columna de fechas de alguna de las tablas de hechos de nuestro proyecto. Supongamos que tenemos una tabla “Facturas” con un campo “fecha de emisión”: Podemos partir de las fechas mínima (desde) y la máxima (hasta) de este campo y crear ésta tabla “Fechas”, añadiendo o quitando días automáticamente de la misma, caso de ser necesario, cada vez que se actualicen los datos. Las funciones MAX y MIN se encargan de ello:

Fechas= CALENDAR (
     MIN ( Facturas[Fecha de emision] );
     MAX ( Facturas[Fecha de emision] )
 )

Como alternativa a MAX-MIN, y sirva como inciso, podríamos utilizar las funciones FIRSTDATE y LASTDATE para reemplazarlas, obteniendo el mismo resultado:

Fechas= CALENDAR (
     FIRSTDATE ( FACTURAS[FECHA EMISION] );
     LASTDATE ( FACTURAS[FECHA EMISION] )
 )

Ambos pares de funciones nos devuelven el mismo resultado, pero el tipo de dato en el que lo hace es diferente:

  • MAX, MIN devuelven un valor.
  • LASTDATE y FIRSTDATE devuelven una tabla con una columna y con una fila como máximo, o vacía.

Por ejemplo, COUNTROWS espera una tabla como argumento; te dará error con MAX-MIN y devolverá un valor o BLANK con las otras. Es importante saber la diferencia por que depende de para qué te convendrá usar unas u otras. En otros artículos lo veremos con mayor detalle.

Pero los proyectos suelen ser más complejos. Siguiendo con el ejemplo de las facturas, puede ser necesario efectuar un previsión de cobros para gestionar la tesorería y para ello se requiere conocer fechas vencimiento o cobro previsto. Lo normal es que esta fecha sea bastante posterior a la de emisión y con MAX de la fecha de emisión van a quedar fechas de cobro fuera del rango. Mejoremos nuestra fórmula, de nuevo usando MAX y MIN:

Fechas= CALENDAR (
     MIN ( MIN ( Facturas[FECHA VENCIMIENTO] ); MIN ( Facturas[FECHA EMISION] ) );
     MAX ( MAX ( Facturas[FECHA VENCIMIENTO] ); MAX ( Facturas[FECHA EMISION] ) )
 )

La tabla resultante podremos vincularla con ambos campos, como puede verse en la imagen, y tenemos la seguridad de que todas las fechas están relacionadas.

A este respecto hay que recordar que Power BI sólo permite una relación activa al mismo tiempo. Te sugiero que leas este post para salvar este supuesto inconveniente.

La función CALENDARAUTO

Al igual que la funcion CALENDAR devuelve una tabla con una columna de fechas automáticamente. Anteriormente especificábamos fechas desde-hasta como parámetros. Esta detecta estas fechas mínima y máxima evaluando las que hayan en campos fecha (no calculados) del modelo.

Sólo espera un parámetro, un entero del 1 al 12, que determina el número de meses, supongamos 3, que se anticipará al fecha mínima (si era 1/5/2017 será 1/2/2017) y que se sumará a la fecha máxima (si era 1/5/2018 será 1/8/2017).

Es un buen punto de partida pero personalmente prefiero tener pleno control sobre lo que hago y no la uso, pero por no dejar de mencionarla.

Formateando la fecha

Este paso que describo ahora suelo realizarlo con prácticamente todos los campos que los requieran en el proyecto, aplicando el tipo de dato que corresponda, fecha, textos, enteros…, Si no se ha detectado adecuadamente, y/o si el formato mostrado no es el deseado.

En este caso, uses CALENDAR o CALENDARAUTO habrás obtenido el mismo resultado una tabla con una columna Date, la cual incluye la secuencia de días dentro del rango definido, pero también incluirá la hora “0:00:00”. No es algo que afecte al funcionamiento o a las relaciones entre tablas, pero me gusta dejar limpios los datos que posteriormente voy a manejar. Por ello seleccionaremos la columna en cuestión y cambiaremos el tipo de dato y/o su formato, como se muestra en la imagen.

Para las fechas me gusta usar el formato “YYYY-MM-DD”… Es una manía heredada de mis experiencias en programación y con bases de datos que seguramente no se justifican en Power BI, pero que de entrada evita confusiones derivadas de configuración regional de fechas (americanos usan mm/dd/yy y europeos dd/mm/yy). Pero puedes usar la que quieras: Lo importante es que el formato no va a afectar a los cálculos.

En un próximo post seguiremos explorando como ir mejorando nuestra tabla de fecha para explotar al máximo todas las posibilidades que T.I. nos ofrece.

  1. Jorge Iribarren E.

    Hola Santiago. Tengo una pregunta para un escenario específico. Tengo mi calendario creado a partir de MIN y MAX de una columna de fechas en una tabla. El problema es que vienen fechas proyectadas por el resto del año que tienen pocos datos y no quiero visualizarlos en el (Slicer) filtro de “Fecha”. Utilicé MIN y TODAY() pero no funciona, además debe ser un día menos, es decir “ayer”. Y no es viable usar Filtro para dejar fuera las fechas que no utilizaré, ya que tendré que estar diariamente agregando una fecha en ese filtro.

    Y la otra pregunta, es si existe algún método para que el PowerBI cuando se actualiza automáticamente, permita que el filtro de fecha muestre siempre el día del reporte y no tengamos que estar manualmente cambiando y republicando?

    Muchas gracias por el aporte.

    • Hola Jorge, como idea probaría a crear una tabla dax que sea una fracción de la tabla fechas original:
      Fechas_Fracción = FILTER(Fechas, Fechas[Fecha]

  2. Buenas:
    consulta estoy empezando en el mundo de PB, el cual estoy haciendo un ejercicio el cual tengo que estableces por rango de vencimiento ….. en 30 dias 60 dias 90 dias y posterior de eso contarlos para graficar y saber cuantas personas estan en condición de vencimiento dentro 30 después por 60 y 90 dias (vencimiento para exámenes ) cada persona tiene 3 tipo de examen diferentes etc cada uno tiene un rol una jerarquía …….
    estoy estancado de como solucionar el asunto de calcular los dias por vencer etc

    • Hola Alejandro, disculpa la tardanza en respuesta. Se me ocurren varias maneras, la mas sencilla y “graficable” podría ser añadir en query editor una columna a la tabla con un datediff que te dé el numero de dias hasta vencimiento, algo así:
      DiasVto = DATEDIFF( [FechaVencimiento],TODAY() , DAY)
      Y añadir otra columna que cree los rangos a partir de ese numero de días, algo como un switch (if dias<30, "Hasta 30" (if dias<60, "Hasta 60"...).
      Esta manera te permite hacer recuento fácilmente, dibujar disribuciones y añadir medidas Dax que calculen el número de dias medio.

  3. Hola Buen día. Soy nueva en Power Bi y me encuentro ante una situación q no he podido resolver. Explico mi problema, tengo una columna con el campo fecha y necesito evaluar si las fechas son mayores al 31/12/2019 entonces ponga 1 y de lo contrario 0.

    Columna=IF (datos [fecha]) > 31/12/2019,1,0)

    El resultado q arroja no es el esperado. Entonces no entiendo como sea la sintaxis.

    Muchas gracias de antemano por la atención.

    • Hola Ana, creo que el problema está en que la fecha has de entrecomillarla (si es un texto) o, usarla en el siguiente modo:
      VAR Fecha = DATE ( 2019, 12, 31 )
      para comparar con otro tipo date. Saludos

  4. Buenas tardes
    Me encuentro delante un nuevo problema de POWER BI y quería saber si me podrías ayudar.

    Seguro que a ti te resulta muy fácil….

    Tengo una tabla de proyectos (futuros) y en columnas € / Fecha inicio / Duración
    He conseguido :
    – calcular fecha fin
    – calcular duración en día
    – €/día
    – crear una tabla calendar

    De ahi queria sacar en POWER BI una proyección temporal de mis futuros ingresos (es decir la suma diaria de € de los proyectos “en curso”)
    Pero en miS intentos solo consigo que sume el día de inicio…

    No sé donde buscar… imagino que es un caso muy habitual pero no encuentro nada…

    puedes ayudarme o bien decirme donde buscar?

    Gracias

    • Hola Florian, si no he pasado nada por alto, tienes una columna con la fecha fin, tabla fechas, Importe por día… Lo tienes todo: Si añades un filtro fechas puedes elegir el periodo a sumar, a pasado o futuro.
      Pero si no es el caso, otra opción es, si tienes el importe por día, con el filtro fecha eliges el periodo (a futuro) y con una medida calcularás el número de dias entre el mismo, por ejemplo:
      NumDias = DATEDIFF(min(Fechas[Date]); max(Fechas[Date]);DAY)
      Dax numdias datediff
      Y creas otra medida que multiplicará este número de días por el importe diario.

Deja un comentario

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