¡Jueves de post! Después de un par de semanas de ausencia debido a diferentes compromisos como el Microsoft Live 365 2023, seguimos con la serie de las funciones ventana y en concreto con la función OFFSET. Para ello, como siempre hacemos es ir a la página de Microsoft para ver que nos cuenta de dicha función y nos dice lo siguiente:
Devuelve una sola fila que se coloca antes o después de la fila actual en la misma tabla, con un desplazamiento determinado. Si la fila actual no se puede deducir como una sola fila, se pueden devolver varias filas.
Dónde:
Y como siempre digo, a vosotros no sé, pero a mí desde luego que no me han sacado de dudas. Vamos a traducirlo a nuestro lenguaje mundano como digo para que lo entendamos todos. La función OFFSET nos va a devolver la fila con el valor anterior/anteriores o posterior/posteriores a nuestro contexto o lo que es lo mismo, nos va a permitir navegar por los datos para irnos a X años atrás o a X años posteriores. Imagina que estás en una fila de gente. Puedes ver a la persona delante de ti y a la persona detrás de ti. Ahora, imagina que tienes la capacidad de “desplazarte” a lo largo de esa fila, hacia adelante o hacia atrás, para ver a diferentes personas. Eso es exactamente lo que hace OFFSET en DAX. Te permite “desplazarte” a través de las filas de una tabla, hacia adelante o hacia atrás, desde la fila actual. ¿Se entiende?
OFFSET toma un número (llamado <delta>) que indica
cuántas filas quieres desplazarte. Un número negativo te lleva hacia atrás y un
número positivo te lleva hacia adelante. También puedes especificar una tabla (<relation>),
cómo ordenar las filas (<orderBy>), cómo manejar los valores en blanco (<blanks>),
cómo dividir la tabla en particiones (<partitionBy>) y cómo hacer
coincidir los datos para identificar la fila actual (<matchBy>). ¡Uf, eso
es un montón de opciones! ¿No creéis?
Vamos a verlo con un ejemplo, que es como se entienden mejor las cosas, y para
eso tenemos nuestro querido Contoso para realizar las pruebas.
Lo primero que hacemos es crear la medida de las ventas totales año de la siguiente manera:
Y arrastramos a nuestro informe:
A la vez, también vamos a sacar un slicer con el filtro de años:
Ahora, lo siguiente que vamos hacer es crear la medida de las ventas del año anterior, para ello:
Y como antes, arrastramos esta medida a nuestra tabla:
Como podemos, nuestra primera venta data del 2007 ya que las ventas del año anterior están en blanco, y nuestra última venta data del año 2009 ya que en el año 2010 las ventas totales están en blanco pero tenemos ventas en la columna del año anterior. Hasta aquí, ningún problema ya que no hemos descubierto la pólvora J Ahora vamos a crear la misma medida con la función OFFSET, para ello, según los parámetros de la medida, debemos indicarle un -1 (año atrás por ser negativo) y la tabla por dónde filtramos y queremos evaluar la medida, por tanto quedaría así ¿no?:
Arrastramos la medida a la tabla y nos devuelve:
¡Uy! Aquí algo no estamos haciendo bien ya que DAX como siempre digo, hace lo que le indicamos… ¿Qué está pasando aquí? Qué estamos respetando el contexto de filtro o lo que es lo mismo, los años que tenemos en la tabla. ¿Cómo podemos “esquivarlos”? ¿Se os ocurre? Claro que sí, con la función ALL sobre los años de nuestra dimensión tiempo. Por tanto, el resultado sería el siguiente:
Y arrastramos al visual y podemos ver:
¡Exactamente! Las ventas del año 2008 que no aparecen en el visual:
Ahora bien, si el año 2008 no lo queremos tener en cuenta para el análisis porque nos puede distorsionar los datos, por ejemplo los datos 2020 en muchos sitios no son “realistas” debido a la COVID-19, ¿cómo lo podemos hacer? Muy fácil, ¿no se os ocurre? Cambiando ALL por ALLSELECTED y dejemos que DAX haga su magia:
Y como por arte de magia, ahora las ventas en la columna OFFSET nos muestran las ventas de la fila ANTERIOR que tenemos en nuestro contexto. ¡Esto nos simplifica un montón nuestro trabajo! ¿No lo creéis?
Por que sí, antes ya lo teníamos resuelto, pero mirar la medida anterior versus con la función OFFSET:
Y nos arroja el mismo resultado:
De primeras, la función OFFSET nos ha simplificado muchísimo nuestras métricas en DAX. ¿Cómo le veis? ¿Bien? Como ya me conocéis, vamos a seguir como siempre dándole una vuelta más de tuerca para exprimir al máximo la función OFFSET. Para eso eliminamos las columnas de Ventas Totales AA y Ventas Anteriores y las filas de Año, y arrastramos a la matriz la columna de año-mes, dándonos como resultado lo siguiente:
¡Uy! ¿Qué está pasando que ahora la medida OFFSET no nos arroja ningún valor? Pues porque le estamos indicando en la métrica que nos la calcule según los años seleccionados pero NO tenemos esa información en la matriz. ¿Cómo lo solucionamos? Cambiando en la métrica la columna de la dimensión fecha tal que así:
Y nos devuelve:
Ahora sí, nos está devolviendo el valor del mes anterior. El siguiente argumento podemos usar es el ORDERBY, este nos va a ordenar los valores en función de lo que especifiquemos. Si le indicamos que nos lo ordene de manera ASC por año-mes, nos queda la siguiente métrica:
Y nos arroja el siguiente resultado:
No nos ha cambiado nada el anterior resultado, el que no teníamos especificado el ORDERBY. Ahora vamos a cambiar el orden de ASC a DESC, a ver qué pasa, ¿no? Para eso, escribimos:
Y nos devuelve:
Ahora vemos que nos hemos desplazado un valor hacia atrás, pero como le indicamos que el orden es DESC, la primera línea con registros en la medida OFFSET es para 200612, justo el valor anterior al 200701. ¿Se ve las posibilidades que nos da la función OFFSET para desplazarnos entre los registros? ¡Una barbaridad!
Ahora, fijaros que la tabla que es la sucesión de meses de
los años con ventas, y ahora os lanzo una pregunta, ¿qué pasa si queremos que
la cuenta se “reinicie” cada año? ¿Cómo lo podemos hacer? Con otro de los
argumentos de la función OFFSET que no es otro que PARTITIONBY, para eso le
indicamos en el PARTITIONBY que sea por CalendarYear de la siguiente manera:
Y nos arroja… UN ERROR:
¿Qué quiere decir? Que la columna por la que le estamos diciendo de que nos ordene los datos no la encuentra en el argumento de <relation>, por lo que bastaría con añadirlo y ya lo tendríamos solucionado. Pero no es tan sencillo, no basta con añadirlo, sino que tenemos que hacer un …… SUMMARIZE de las columnas año y año-mes de la dimensión tiempo tal que así:
Y esto nos arroja:
Como podemos ver, nos adelantamos una posición a la realidad y además para el 200712, el valor es blanco dado que le hemos dicho que se “reinicie” cada año. ¿Cómo lo veis? OFFSET es como un superpoder en DAX que nos permite desplazarnos a través de las filas de una tabla. Ya sea que estemos buscando patrones en nuestros datos, realizando cálculos complejos o simplemente explorando, OFFSET puede ser nuestro mejor amigo. Así que la próxima vez que te encuentres frente a un desafío en DAX, recuerda: ¡OFFSET al rescate!
¡Nos vemos en los datos!
