Cálculo del número de días entre compras

Partimos de un listado de ventas (Sales) en el que se indica -por ejemplo- el identificador de cliente y las fechas de las compras:

El objetivo es añadir a esta tabla una columna que indique el número de días transcurridos desde la compra anterior (anterior en el tiempo, pues nada nos asegura que el orden de los datos en la tabla original sea cronológico, y ya sabemos que Power BI tampoco asegura el orden de las filas).

La resolución de este escenario no es sencilla. Aun cuando podría resolverse en una única expresión, se plantea a continuación su resolución paso a paso. Las fases involucradas son las siguientes:

1. Es necesario obtener, para cada fecha de compra del cliente X, la fecha de la anterior compra.
2. La anterior fecha de compra es la mayor fecha de todas las compras del cliente X (exceptuando la compra que estemos analizando, por supuesto).
3. Podemos obtener todas las compras anteriores del cliente X realizando una combinación cruzada de la tabla consigo misma tomando como campo común el correspondiente al identificador del cliente y considerando en la tabla de la derecha (véase la documentación de la función GENERATE) solo las fechas anteriores a la de la fecha de compra siendo considerada en la tabla izquierda. Así, por ejemplo, para la compra que aparece en la tercera línea de la imagen anterior (correspondiente al cliente 1, con fecha de 21 de enero de 2019), una combinación cruzada con la misma tabla en las condiciones mencionadas devolvería una fila para la combinación de la fila 3 y la fila 1 (mismo cliente y fecha anterior) y otra para la combinación de la fila 3 y la fila 2 (de nuevo, mismo cliente y fecha anterior).

Por último, una vez obtenida la tabla mencionada en el punto 1, bastaría calcular la diferencia entre cada fecha de compra y la fecha de la última compra.

Vamos punto por punto.

En primer lugar queremos obtener, para cada fila (es decir, para cada compra), el conjunto de compras anteriores del mismo cliente. Esto, como se ha comentado, puede lograrse con una combinación cruzada que nos permita especificar las condiciones de la combinación, es decir, con la función GENERATE. Como esta función requiere que las tablas involucradas en la combinación tengan campos con nombres diferentes, vamos modificar los nombres de los campos de la segunda copia con la función SELECTCOLUMNS, añadiendo un símbolo "_" al final de los nombres:

Tal y como se ve, estamos obteniendo la combinación cruzada imponiendo la condición de que el identificador de cliente sea el mismo, y que las fechas de la columna de la derecha sean inferiores a la fecha considerada en cada una de las filas de la izquierda (para simplificar, estamos suponiendo que un mismo cliente no va a realizar dos compras el mismo día).

Obsérvese que no aparecen los registros de la primera tabla para los que no hay registros en la segunda que cumplan las condiciones impuestas (es decir, en la tabla que acabamos de crear, solo se incluyen fechas de compra para las que hay alguna compra anterior).

Obtener, a partir de aquí, la fecha anterior a cada compra es fácil: basta con agrupar (con la función GROUPBY) por cliente y fecha de compra (campos Customer y Purchase Date) y seleccionar el valor máximo (de cada grupo) del campo Purchase Date_:

Ahora viene un proceso un tanto complicado: como en la tabla solo se incluyen fechas de compra para las que existe una compra anterior, para que en el resultado final aparezcan todas las fechas originales tendríamos que realizar una combinación externa izquierda entre la tabla de fechas original y la nueva. Para esto tendríamos que usar la función NATURALLEFTOUTERJOIN, pero esta función exige que las tablas a combinar tengan la misma ascendencia (procedan de la misma fuente), lo que no se cumple en nuestro caso. Tal y como se explica en la documentación de esta función, es posible resolver este problema añadiendo una cadena de texto vacía al final de los nombres:

Por último, para calcular el número de días desde la última compra basta con añadir una nueva columna que devuelva la diferencia (solo si existe una última compra):

Para devolver la diferencia de días comentada utilizamos la función IF para confirmar si el valor del campo correspondiente a la última compra es un BLANK o no, para lo que utilizamos la función ISBLANK.