¡Jueves! Arriba ese ánimo que viene otra entrada sobre la inteligencia temporal ;). En esta entrada intentaré abarcar un amplio abanico de funciones de inteligencia de tiempo, no todas ya que si no os tengo leyendo de aquí hasta final de año :)
Si te perdiste el post sobre cómo crear una tabla calendario te dejo aquí el post para que lo leas antes de empezar. Y te recomiendo con mi mejor intención que leas antes también los post de orden de evaluación de CALCULATE y los iteradores.
Una vez hecha esta pequeña introducción de “recomendaciones” pasamos a lo que más nos gusta, que es meternos en harina con Power BI. Para ello abrimos nuestra modelo de Contoso en el que tenemos creado nuestra tabla de calendario.
Tenemos nuestra famosa medida de Ventas:
Y la arrastramos a nuestro informe sobre la visual tabla que la tenemos con la granularidad de mes:
Podemos ver que nos muestra el total de ventas por mes del año 2007, y esto es posible a las relaciones que tenemos establecidas en nuestro modelo (recordar… siempre que sea posible, haz un modelo en estrella):
Ahora queremos calcular el acumulado del año, es decir, que nos haga el acumulado en función del contexto que tengamos definido. Para eso existe la función DATESYTD que devuelve una tabla que contiene una columna con las fechas del año hasta la fecha, en el contexto actual.
Donde:
<dates> es una columna que contiene las fechas
<year_end_date> es opcional y define la fecha de finalización del año. El valor predeterminado es el 31 de diciembre.
Por lo que escribimos la siguiente métrica:
Y la arrastramos a nuestra visual:
Y como podemos ver, nos está devolviendo el valor acumulado hasta la fecha (31 de diciembre de 2007) o lo que es lo mismo:
Enero = Enero
Febrero = Enero + Febrero
Marzo = Enero + Febrero + Marzo
…
Y ahora, como habéis repasado los post de la entrada, me podréis decir: Javi, se puede calcular el acumulado con la función FILTER. Y la respuesta es… SÍ, de la siguiente manera:
Y nos devuelve:
Como se puede ver, es el mismo resultado, y eso se debe a que la función FILTER la estamos utilizando como argumento de filtro de la función CALCULATE y nos devuelve un conjunto de fechas que reemplaza la selección de la tabla DimDate. Es decir, se modifica el contexto de filtro por la medida y calcula el valor en un conjunto diferente de fechas. ¿Se entiende no?
Entonces os estaréis preguntado, si con una función como es DATESYTD se calcula lo mismo que con la función FILTER, ¿por qué os lo he mostrado? Para ver y entender el funcionamiento de DAX.
También tenemos la función DATESMTD, que nos devuelve una tabla que contiene una columna con las fechas del mes hasta la fecha, en el contexto actual y cuya sintaxis es:
Por lo que realizamos lo mismo que para la anterior medida modificando la función de manera que nos queda:
Y esta función nos devuelve:
El valor de las ventas totales por cada mes, es decir, lo mismo que la medida Total Ventas. ¿Seguros? No, nos devuelve lo mismo en la última fila Total, sino que nos devuelve el valor del último mes.
Bien, hasta aquí como podéis ver, hemos tenido que envolver todas las funciones con la función CALCULATE, pero DAX nos tiene reservado unas funciones que simplifican más aún si cabe la sintaxis y son las siguientes:
TOTALYTD Evalúa el valor anual hasta la fecha de la expresión en el contexto actual cuya sintaxis es:
<expression> es la expresión que devuelve un valor escalar.
<dates> es la columna que contiene los campos fechas.
<filter> es la expresión que especifica un filtro que se va a aplicar al contexto actual (no es la función FILTER).
<year_end_date> es una cadena literal con una fecha que define la fecha de finalización del año. El valor predeterminado es el 31 de diciembre.
Por tanto, ahora si escribimos la siguiente métrica:
Esta nos devuelve:
Como podéis ver, está métrica se comporta igual que la medida DATESYTD. La función TOTALYTD ejecuta por detrás la función CALCULATE, lo cual es una buena razón para que no abusemos de ella.
Buena práctica: siempre que CALCULATE esté presente en el código, hacerlo evidente siempre es una buena práctica, por ejemplo, por la transición de contexto que implica.
Por no enrollarme demasiado, es lo mismo para las funciones TOTALQTD y TOTALMTD. Ahora bien, desde mi punto de vista, vamos ahora con algo que es muy útil para cualquier análisis que requiera de la inteligencia temporal, y es la comparativa con períodos pasados.
Por ejemplo, para realizar la comparación con un periodo idéntico pero del año anterior, tenemos la función SAMEPERIODLASTYEAR. Devuelve una tabla que contiene una columna de fechas desplazadas un año atrás en el tiempo desde las fechas de la columna fecha especificada, en el contexto actual cuya sintaxis es:
Por tanto, para calcular el total de las ventas del año anterior escribimos la siguiente medida:
Y la arrastramos a nuestra tabla:
Como podemos ver, la columna Total Ventas Año Anterior nos devuelve el valor para cada mes del año 2007 mientras que la columna Total Ventas nos devuelve el valor de las ventas del 2008. Bien, siguiendo la casuística del cálculo anterior, ¿cómo calcularíamos el trimestre anterior? ¿Se os ocurre? ¿SAMEPERIODLASTQUARTER?... Error, no existe esa función J para ello tenemos que usar la función DATEADD, devuelve una tabla que contiene una columna de fechas que se han desplazado hacia delante o hacia atrás en el tiempo según el número especificado de intervalos desde las fechas del contexto actual, cuya sintaxis es:
Dónde:
<dates> es una columna que contiene fechas.
<number_of_intervals> es un entero que especifica el número de intervalos que se van a sumar o restar a las fechas.
<interval> Intervalo por el que se van a desplazar las fechas. El valor del intervalo puede ser uno de los siguientes: year, quarter, month o day.
Por lo que si escribimos la siguiente métrica:
Y la arrastramos a nuestra visual:
Si quisiéramos ir 1 mes en el tiempo hacia atrás, en el argumento en vez de YEAR especificaríamos MONTH.
DATEADD es una función desde mi punto de vista más potente que SAMEPERIODLASTYEAR porque
con una sentencia muy similar, variando sólo los argumentos, podemos calcular
el valor de un trimestre, mes o día anterior. ¿No os parece?
Y por hoy ya es suficiente, que sino termino hoy con todo y todos y no es plan. ;)
¡Nos vemos en los datos!
