Recientemente me preguntaron por un curioso requerimiento que sonaba sencillo en un principio pero cuando lo analicé note que necesitaría escribir más líneas de las pensadas.
A medida que las instituciones o entidades guardan más datos en el tiempo se vuelve más difícil resaltar valores que normalmente lo veríamos con una tabla. En este caso particular querían conocer cuantos días consecutivos venían creciendo las ventas respecto del anterior. Normalmente la racha de ventas se vería en una simple tabla diaria pintando colores y filtrando por mes. Sin embargo que pasaría si quisiéramos tener un tablero de pantallazo anual con una tarjeta que diga cual fue la mejor racha en días de ventas.
El siguiente artículo muestra como contar esas rachas de ventas para obtener el máximo valor en una tarjeta.
Para desarrollar esta medida vamos a nutrirnos del ejemplo más sencillo posible. Una tabla de ventas diaria para distintas tiendas. Podemos filtrar un rango de días y store. Nuestro objetivo sería una tarjeta que nos diga la mejor racha de ventas. La definición concreta sería cantidad de días consecutivos que vendieron más que el día anterior. Con la condición definida veamos el primer pantallazo del ejemplo:
La imagen representa los filtros antes mencionado y la mejor racha de tres días consecutivos creciendo en ventas. Para comprender de manera más sencilla el crecimiento generé una medida que muestre el porcentaje de crecimiento respecto al día anterior de la siguiente manera:
Crecimiento Diario = DIVIDE( [Venta Diaria] - [Venta Ayer] , [Venta Ayer] , 0)
Hasta aquí vemos una clásica fórmula de crecimiento (formateada a porcentaje) que devolverá un número mayor a cero cuando la venta del día sea superior a la del día anterior.
Naturalmente en un entorno de programación clásico pensaríamos en un loop. Iterar la tabla aumentando un contador cada vez que el crecimiento sea positivo y reseteándolo cuando no lo sea tras guardar el máximo valor. Un desarrollo así en DAX sería muy complicado y costoso (performance).
Para resolverlo por DAX vamos a tomar una lógica de consultas. Para conocer el máximo contador vamos a resolver una medida que obtenga el resultado para cada día. Me refiero a que podamos saber en cada fila de nuestra tabla cuantos días lleva nuestra racha. Esto será más sencillo si cambiamos la lógica. Lo que necesitaríamos es conocer el máximo día de crecimiento negativo menor a la fecha y fila donde estoy parado. De esa manera podríamos restar la fecha de la fila con la antes mencionada para que su resta de días nos informe cuantos días llevamos en esto. Veamos como sería esa fecha antes de saltar a la medida:
MAXX (
FILTER ( ALLSELECTED(TablaFecha),
[Crecimiento Diario] < 0
&& [Venta Diaria]<>BLANK()
&& TablaFecha[Fecha] < __selecteddate
),
TablaFecha[Fecha]
)
Da ese modo veremos la máxima fecha para todas las fechas dentro de la visualización afectada por otras visualizaciones cuyo crecimiento sea menor a cero y tenga venta siempre y cuando sea menor a la cual en donde estoy parado. Por ejemplo si nos paramos en la siguiente fecha deberíamos obtener una fecha de 3 días antes con la lógica antes mencionada porque es la máxima con crecimiento diario negativo que sea menor a la actual donde estamos parados:
Si tomamos el ejemplo del 13 de enero y aplicamos la lógica deberíamos obtener un 10 de enero. De ese modo podríamos restar las fechas para saber que ese día llevábamos una racha de 3 días creciendo en ventas. SI nos paramos el 12 de enero llevaba 2 días creciendo y si nos paramos el 11 de enero llevaba 1 día creciendo. Con esta idea construimos la siguiente medida:
Dias Consecutivos =
VAR __selecteddate =
SELECTEDVALUE ( TablaFecha[Fecha] )
VAR __firstcontextdate =
MINX (
ALLSELECTED(TablaFecha[Fecha]),
TablaFecha[Fecha]
)
VAR __lastnotworkeddate =
MAXX (
FILTER ( ALLSELECTED(TablaFecha),
[Crecimiento Diario] < 0 && [Venta Diaria]<>BLANK() && TablaFecha[Fecha] < __selecteddate
),
TablaFecha[Fecha]
)
VAR __diff =
IF(ISBLANK(__lastnotworkeddate)
, DATEDIFF (__firstcontextdate, __selecteddate, DAY ) +1
, DATEDIFF (__lastnotworkeddate, __selecteddate, DAY )
)
RETURN
IF ( [Crecimiento Diario] > 0, IF ( __diff > 0, __diff ) )
Analicemos los pasos. Primero creamos una variable para obtener la fecha en la cual estamos posicionados en la tabla o contexto para restarla por la magia que pensamos antes. Aparece un escenario particular que es __firstcontextdate para la situación de la primera fecha de nuestro contexto porque no tendría una "anterior" la cual consultar. Para los otros casos distintos a la primera hacemos una variable con la misma lógica que aplicamos antes. Vamos a usar una última variable para restar las fechas la cual tendría una condición preguntando si una es blanca para definir que es la primera fecha.
Para cerrar y hacer más efectivo todo el script vamos a terminar con un IF en nuestro return que solo ejecute valores para las filas que tengan crecimiento.
Llegaríamos a un resultado así:
Nuestra medida podrá calcular en un contexto de filas diaria cual es la racha de venta. Destaco el contexto de filas porque no funcionaría sin él. Si intentamos colocar la medida en una tarjeta no funcionaría a menos que nuestra página filtre un día exacto. Es por ello que todavía no terminamos puesto que queríamos tener un contexto anual en el cual podamos conocer la máxima racha.
El desarrollo restante es bastante sencillo. Se basa en generar un contexto de filas diaria ejecutando la medida a la cual aplicarle una agregación MAX. Algo semejante a lo que vimos en el post de totales. Dejando un código así:
Maximos dias consecutivos creciendo =
MAXX(
ADDCOLUMNS(
SUMMARIZE(TablaFecha, TablaFecha[Fecha])
, "@Dias", [Dias Consecutivos]
), [@Dias]
)
La medida genera un contexto diario de la tabla fecha la cual calcula Días Consecutivos para cada día culminando con la agregación MAX que nos traiga el máximo valor de ellos calculado. De ese modo llegaríamos al resultado de la tarjeta de la mejor racha en un periodo personalizado:
Finalmente podemos quitar la tabla que usamos para comprender mejor nuestro desarrollo y llegar a la tarjeta que indique los días consecutivos de crecimiento de ventas que queríamos en nuestro informe.
Espero que la explicación haya sido clara y los ayude en estos casos. Si no lo fue sentite libre de comentar en el post o revisar el desarrollo posteado en mi github. El archivo .pbix cuenta con una segunda página para hacer el mismo conteo consecutivo para crecimiento de meses en lugar de días.