Excel DAX: inteligencia de tiempo

DAX tiene una característica importante y poderosa, conocida como Time Intelligence. La inteligencia de tiempo le permite escribir fórmulas DAX que se refieren a los períodos de tiempo para su uso en las tablas dinámicas.

DAX tiene 35 funciones de inteligencia de tiempo específicamente para agregar y comparar datos a lo largo del tiempo. Sin embargo, estas funciones de DAX tienen algunas restricciones sobre los datos que debe comprender y trabajar con precaución para evitar errores.

¿Por qué la inteligencia de tiempo hace que DAX sea poderoso?

Las funciones de inteligencia de tiempo funcionan con datos que cambian constantemente, según el contexto que seleccione en las tablas dinámicas y las visualizaciones de Power View. Como ya sabe, la mayor parte del análisis de datos implica resumir los datos durante períodos de tiempo, comparar los valores de los datos en los períodos de tiempo, comprender las tendencias y tomar decisiones basadas en proyecciones futuras.

Por ejemplo, es posible que desee sumar los importes de ventas del último mes por productos y comparar los totales con los de otros meses del año fiscal. Esto significa que debe usar las fechas como una forma de agrupar y agregar transacciones de ventas para un período particular en el tiempo.

Aquí es donde puede observar el poder de DAX. Puede utilizar las funciones de inteligencia de tiempo de DAX para definir campos calculados que le ayuden a analizar los datos a lo largo del tiempo, sin tener que cambiar las selecciones de fecha en las tablas dinámicas. Esto facilita su trabajo. Además, puede crear tablas dinámicas que no serían posibles de otra manera.

Requisitos para las funciones de inteligencia de tiempo de DAX

Las funciones de inteligencia de tiempo de DAX tienen ciertos requisitos. Si no se cumplen estos requisitos, es posible que obtenga errores o que no funcionen correctamente. Por lo tanto, también puede hacer referencia a estos requisitos como reglas o restricciones. A continuación se muestran ciertos requisitos / reglas / restricciones de las funciones de inteligencia de tiempo de DAX:

  • Necesita tener una tabla de fechas en su modelo de datos.

  • La tabla de fechas debe incluir una columna considerada por DAX como la columna Fecha. Puede nombrar la columna de la manera que desee, pero debe cumplir con las siguientes condiciones: o La columna de fecha debe contener un conjunto contiguo de fechas que cubra todos los días en el período de tiempo que está analizando los datos.

    • Cada fecha debe existir una vez y solo una vez en la columna de la fecha.

    • No puede omitir ninguna fecha (por ejemplo, no puede omitir fechas de fin de semana).

  • Las funciones de inteligencia de tiempo de DAX funcionan solo en un calendario estándar y asumen el inicio del año como el 1 de enero y el final del año como el 31 de diciembre, con los meses del año y los días de cada mes como un año calendario.

Sin embargo, puede personalizar un calendario estándar para diferentes ejercicios económicos. Es una buena práctica verificar los requisitos anteriores antes de utilizar cualquier función de inteligencia.

Para obtener más detalles sobre las tablas de fechas y su uso en las fórmulas de DAX, consulte el tutorial = Modelado de datos con DAX en esta biblioteca de tutoriales.

Funciones de inteligencia de tiempo de DAX: categorías

Las funciones de DAX Time Intelligence se pueden clasificar de la siguiente manera:

  • Funciones DAX que devuelven una sola fecha.
  • Funciones DAX que devuelven una tabla de fechas.
  • Funciones DAX que evalúan expresiones durante un período de tiempo.

Funciones de DAX que devuelven una sola fecha

Las funciones de DAX en esta categoría devuelven una sola fecha.

Hay 10 funciones DAX en esta categoría:

No Señor. Función DAX y valor de retorno
1

FIRSTDATE (Date_Column)

Devuelve la primera fecha en Date_Column en el contexto actual.
2

LASTDATE (Date_Column)

Devuelve la última fecha de Date_Column en el contexto actual.
3

FIRSTNONBLANK (Date_Column, Expression)

Devuelve la primera fecha en la que una expresión tiene un valor que no está en blanco.
4

LASTNONBLANK (Date_Column, Expression)

Devuelve la última fecha en la que una expresión tiene un valor que no está en blanco.
5

STARTOFMONTH (Date_Column)

Devuelve la primera fecha de un mes en el contexto actual.
6

ENDOFMONTH (Date_Column)

Devuelve la última fecha de un mes en el contexto actual.
7

STARTOFQUARTER (Date_Column)

Devuelve la primera fecha de un trimestre en el contexto actual.
8

ENDOFQUARTER (Date_Column)

Devuelve la última fecha de un trimestre en el contexto actual.
9

STARTOFYEAR (Date_Column, [YE_Date])

Devuelve la primera fecha de un año en el contexto actual.
10

ENDOFYEAR (Date_Column, [YE_Date])

Devuelve la última fecha de un año en el contexto actual.

Funciones de DAX que devuelven una tabla de fechas

Las funciones DAX de esta categoría devuelven una tabla de fechas. Estas funciones se utilizarán principalmente como un argumento SetFilter para la función DAX - CALCULATE.

Hay 16 funciones DAX en esta categoría. Ocho (8) de estas funciones de DAX son las funciones "anterior" y "siguiente".

  • Las funciones “anterior” y “siguiente” comienzan con una columna de fecha en el contexto actual y calculan el día, mes, trimestre o año anterior o siguiente.

  • Las funciones "anteriores" funcionan hacia atrás desde la primera fecha en el contexto actual y las funciones "siguientes" avanzan desde la última fecha en el contexto actual.

  • Las funciones "anterior" y "siguiente" devuelven las fechas resultantes en forma de una tabla de una sola columna.

No Señor. Función DAX y valor de retorno
1

PREVIOUSDAY (Date_Column)

Devuelve una tabla que contiene una columna de todas las fechas que representan el día anterior a la primera fecha en Date_Column en el contexto actual.
2

NEXTDAY (Date_Column)

Devuelve una tabla que contiene una columna de todas las fechas del día siguiente, según la primera fecha especificada en Date_Column en el contexto actual.
3

PREVIOUSMONTH (Date_Column)

Devuelve una tabla que contiene una columna de todas las fechas del mes anterior, basada en la primera fecha en Date_Column en el contexto actual.
4

NEXTMONTH (Date_Column)

Devuelve una tabla que contiene una columna de todas las fechas del mes siguiente, según la primera fecha de Date_Column en el contexto actual.
5

PREVIOUSQUARTER (Date_Column)

Devuelve una tabla que contiene una columna de todas las fechas del trimestre anterior, basada en la primera fecha en Date_Column en el contexto actual.
6

NEXTQUARTER (Date_Column)

Devuelve una tabla que contiene una columna de todas las fechas del próximo trimestre, según la primera fecha especificada en Date_Column en el contexto actual.
7

PREVIOUSYEAR (Date_Column, [YE_Date])

Devuelve una tabla que contiene una columna de todas las fechas del año anterior, dada la última fecha en Date_Column en el contexto actual.
8

NEXTYEAR (Date_Column, [YE_Date])

Devuelve una tabla que contiene una columna de todas las fechas del año siguiente, según la primera fecha de Date_Column en el contexto actual.

Cuatro (4) funciones DAX calculan un conjunto de fechas en un período. Estas funciones realizan los cálculos utilizando la última fecha en el contexto actual.

No Señor. Función DAX y valor de retorno
1

DATESMTD (Date_Column)

Devuelve una tabla que contiene una columna de las fechas del mes hasta la fecha, en el contexto actual.
2

DATESQTD (Date_Column)

Devuelve una tabla que contiene una columna de las fechas del trimestre hasta la fecha, en el contexto actual.
3

DATESYTD (Date_Column, [YE_Date])

Devuelve una tabla que contiene una columna de las fechas del año hasta la fecha, en el contexto actual.
4

SAMEPERIODLASTYEAR (Date_Column)

Devuelve una tabla que contiene una columna de fechas desplazadas un año atrás en el tiempo desde las fechas en la columna Date_Column especificada, en el contexto actual.

Note- SAMEPERIODLASTYEAR requiere que el contexto actual contenga un conjunto contiguo de fechas.

Si el contexto actual no es un conjunto contiguo de fechas, SAMEPERIODLASTYEAR devolverá un error.

  • Se utilizan cuatro (4) funciones DAX para pasar del conjunto de fechas que están en el contexto actual a un nuevo conjunto de fechas.

    Estas funciones de DAX son más potentes que las anteriores.

    • Las funciones de DAX - DATEADD, DATESINPERIOD y PARALLELPERIOD cambian cierto número de intervalos de tiempo del contexto actual. El intervalo puede ser día, mes, trimestre o año, representado por las palabras clave: DÍA, MES, TRIMESTRE y AÑO respectivamente.

      Por ejemplo:

  • Retroceda 2 días.

  • Avanza 5 meses.

  • Avanza un mes a partir de hoy.

  • Regrese al mismo trimestre del año pasado.

      Si el argumento de la función - número de intervalos (valor entero) es positivo, el desplazamiento es hacia adelante y si es negativo, el desplazamiento es hacia atrás.

    • Función DAX - DATESBETWEEN calcula el conjunto de fechas entre la fecha de inicio especificada y la fecha de finalización.

No Señor. Función DAX y valor de retorno
1

DATEADD (Date_Column, Number_of_Intervals, Interval)

Devuelve una tabla que contiene una columna de fechas, desplazada hacia adelante o hacia atrás en el tiempo por el número especificado de intervalos de las fechas en el contexto actual.
2

DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval)

Devuelve una tabla que contiene una columna de fechas que comienza con fecha_inicio y continúa durante el número_de_intervalos especificado.
3

PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval)

Devuelve una tabla que contiene una columna de fechas que representa un período paralelo a las fechas en la columna Date_Column especificada en el contexto actual, con las fechas desplazadas un número de intervalos hacia adelante o hacia atrás en el tiempo.
4

DATESBETWEEN (Date_Column, Start_Date, End_Date)

Devuelve una tabla que contiene una columna de fechas que comienza con start_date y continúa hasta end_date.

Funciones de DAX que evalúan expresiones durante un período de tiempo

Las funciones DAX de esta categoría evalúan una expresión durante un período de tiempo específico.

Hay nueve (9) funciones DAX en esta categoría:

  • Se pueden usar tres (3) funciones DAX en esta categoría para evaluar cualquier expresión dada durante un período de tiempo específico.

No Señor. Función DAX y valor de retorno
1

TOTALMTD (Expression, Date_Column, [SetFilter])

Evalúa el valor de la expresión para las fechas del mes hasta la fecha, en el contexto actual.
2

TOTALQTD (Expression, Date_Column, [SetFilter])

Evalúa el valor de la expresión para las fechas del trimestre hasta la fecha, en el contexto actual.
3

TOTALYTD (Expression, Date_Column, [SetFilter], [YE_Date])

Evalúa el valor de la expresión para las fechas del año hasta la fecha, en el contexto actual

  • Se pueden utilizar seis (6) funciones DAX en esta categoría para calcular los saldos de apertura y cierre.

    • El saldo inicial de cualquier período es el mismo que el saldo final del período anterior.

    • El saldo de cierre incluye todos los datos hasta el final del período, mientras que el saldo de apertura no incluye ningún dato del período actual.

    • Estas funciones DAX siempre devuelven el valor de una expresión evaluada para un momento específico.

  • El momento que nos importa es siempre el último valor de fecha posible en un período de calendario.

  • El saldo inicial se basa en la última fecha del período anterior, mientras que el saldo final se basa en la última fecha del período actual.

  • El período actual siempre está determinado por la última fecha en el contexto de la fecha actual.

No Señor. Función DAX y valor de retorno
1

OPENINGBALANCEMONTH (Expression, Date_Column, [SetFilter])

Evalúa la expresión en la primera fecha del mes en el contexto actual.
2

CLOSINGBALANCEMONTH (Expression, Date_Column, [SetFilter])

Evalúa la expresión en la última fecha del mes en el contexto actual.
3

OPENINGBALANCEQUARTER (Expression, Date_Column, [SetFilter])

Evalúa la expresión en la primera fecha del trimestre, en el contexto actual.
4

CLOSINGBALANCEQUARTER (Expression, Date_Column, [SetFilter])

Evalúa la expresión en la última fecha del trimestre en el contexto actual.
5

OPENINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date])

Evalúa la expresión en la primera fecha del año en el contexto actual.
6

CLOSINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date])

Evalúa la expresión en la última fecha del año en el contexto actual.