por Xesco Abad 2019-04-10

Trabajando con Funciones de TIME – INTELLIGENCE – Serie DimDate (2/3)

Uno de los puntos fuertes que tiene el lenguaje DAX es que nos permite comparar datos por periodos de tiempo de forma sencilla y rápida gracias a sus funciones de inteligencia de tiempo. Con las funciones de inteligencia de tiempo podemos reducir una consulta complicada de T-SQL de 50 lineas a una sola linea de código.

En este post describiremos las funciones de Time Intelligence más utilizadas con sus respectivos casos prácticos.

1. SAMEPERIODLASTYEAR

Posiblemente se trate de la función de inteligencia de tiempo más utilizada. Gracias a este tipo de uso, podemos comparar un importe entre un periodo de tiempo, con su importe en el mismo periodo de tiempo pero del año anterior.

Para utilizarla simplemente tenemos que crear una medida, y en la parte del filtro poner la función SAMEPERIODLASTYEAR con el parámetro de tipo fecha por el que queramos comparar, por defecto pondremos la columna de tipo fecha de nuestra dimensión de tiempo.

Total Gross SAMEPERIODLASTYEAR = 
CALCULATE(SUM('Disney Movies'[Total Gross]);SAMEPERIODLASTYEAR(DimDate[Date]))

Una vez presentemos esta medida a nuestro cliente, lo primero que nos indicará es que quiere el KPI del porcentaje de mejora respecto al año anterior. Para realizar esta medidas en nuestro caso dividiremos “Total Gross” del periodo seleccionado / “Total Gros” del mismo periodo del año anterior.Transact-SQL

% Total Gross SAMEPERIODLASTYEAR=
 DIVIDE( SUM('Disney Movies'[Total Gross]);CALCULATE(SUM('Disney Movies'[Total Gross]);SAMEPERIODLASTYEAR(DimDate[Date])))-1

2. TOTALYTD, TOTALQTD & TOTALMTD

Cuando hablamos de importes acumulados debemos pensar siempre en las siguientes interpretaciones:

TOTALYTD

Esta función evalúa la expresión a lo largo del intervalo que empieza el primer día del año y termina con la última fecha después de aplicar los filtros especificados.

Total Gross TOTALYTD =
 TOTALYTD(SUM('Disney Movies'[Total Gross]);DimDate[Date])

TOTALQTD

Evalúa la expresión a lo largo del intervalo que empieza el primer día del trimestre y termina con la última fecha después de aplicar los filtros especificados.

Total Gross TOTALQTD =
 TOTALQTD(SUM('Disney Movies'[Total Gross]);DimDate[Date])

TOTALMTD

Al igual que las dos funciones anteriores evalúa la expresión a lo largo del intervalo que empieza al principio del mes y termina con la última fecha después de aplicar los filtros especificados.

Total Gross TOTALMTD =
 TOTALMTD(SUM('Disney Movies'[Total Gross]);DimDate[Date])

3. PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER & PREVIOUSYEAR

Estas funciones, como bien indica el nombre, devuelven la información del día, mes, cuatrimestre o año anterior.

Total Gross PREVIOUSMONTH =
 CALCULATE(SUM('Disney Movies'[Total Gross]);PREVIOUSMONTH(DimDate[Date]))

NEXTDAY, NEXTMONTH, NEXTQUARTER & NEXTYEAR son sus equivalentes para el siguiente día, mes, cuatrimestre o año.

4. DATEDIFF

A través de esta función podemos averiguar el intervalo de tiempo que hay entre dos fechas.

Los intervalos de tiempo que nos puede devolver esta función son los siguientes:

SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

Uno de los casos prácticos donde se utiliza DATEDIFF es para el calculo de la media por un intervalo de tiempo especifico, ya que, por defecto, la tabla de hechos está relacionada con la dimensión de tiempo por una columna de tipo Date por lo que nos calcularía la media por día. Si quisiéramos calcular la media por hora la expresión de la medida seria el siguiente:

AVG Hour =
 DIVIDE(SUM('Disney Movies'[Total Gross]);DATEDIFF(MAX(DimDate[Date]);MIN(DimDate[Date]);HOUR))
1
2
AVG Hour =

5. DATEADD

Siempre que queramos añadir un intervalo de tiempo a una fecha, esta es la función que debemos utilizar. Un caso practico podría ser que un cliente tuviera un problema hace 2 años y quisiera comparar la producción actual con la de hace 2 años. Para realizar esta medida utilizaríamos la función SAMEPERIODLASTYEAR pero en la parte donde añadimos el parámetro de la fecha, añadiríamos la fecha pero de hace 2 años.

Total Gross SAMEPERIODLAST 2 YEARS =
 CALCULATE(SUM('Disney Movies'[Total Gross]);SAMEPERIODLASTYEAR(DATEADD(DimDate[Date]; -2;YEAR)))

Otras funciones que también nos pueden servir de ayuda para realizar consultas DAX son las siguiente:

TODAY: Devuelve la fecha actual.
NOW: Devuelve la fecha actual con horas, minutos y segundos.
FIRSTDATE: Devuelve la primera fecha.
LASTDATE: Devuelve la ultima fecha.
FIRSTNONBLANK: Devuelve el primer valor que no está en blanco.
LASTNONBLANK: Devuelve el ultimo valor que no está en blanco.
DATESMTD, DATESQTD & DATESYTD: Devuelve un conjunto de fechas del mes, cuatrimestre o año hasta la fecha actual.
ENDOFMONTH, ENDOFQUARTER & ENDOFYEAR: Devuelve el final del mes, cuatrimestre o año.
STARTOFMONTH,STARTOFQUARTER & STARTOFYEAR: Devuelve el inicio del mes,trimestre o año.