Número de días entre 2 transacciones con DAX

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: intermedio

Hoy me corté el pelo (bastante elegante también, incluso si lo digo yo mismo). Mientras charlaba, le pregunté a mi peluquero “en promedio, ¿con qué frecuencia debo cortarme el pelo”? Ella me dijo (para hombres) alrededor de 4-6 semanas. Luego me puse a pensar (como lo hago), me pregunto si podría extraer datos de mi tarjeta de crédito usando Power BI y averiguar con qué frecuencia realmente me corto el cabello. Resulta que pude hacer esto, y este artículo explica la parte más difícil de esa tarea: encontrar la cantidad de días entre dos fechas de transacción usando DAX.

Cargando datos de tarjetas de crédito

Ya he cubierto cómo cargar sus datos bancarios usando Power Query antes, así que no voy a cubrir eso en profundidad en este artículo. Primero actualicé los extractos de mi tarjeta de crédito bancaria para tener un conjunto completo de datos y luego los cargué en Power BI. Después de cargar los datos en una sola tabla, tenía la siguiente estructura de datos.

image_thumb-8-7086276

Como puede ver, es una lista simple de transacciones con una fecha, descripción (proveedor) y el monto de la transacción. Con esto en mente, mi idea fue filtrar primero la descripción a la de mi peluquero y luego averiguar el número de días entre transacciones. Tenga en cuenta que cambié el verdadero nombre de mi peluquero en los extractos de datos

Verifique que los datos estén completos

Lo primero que hice fue usar Power BI para verificar que los datos estuvieran completos. No es necesario escribir ningún DAX para hacer esto. Acabo de crear un par de imágenes que me mostraron un COUNT de descripción por fecha.

image_thumb-9-4385976

Visual 1 es simplemente la columna de fecha con un recuento de descripción. Power BI crea automáticamente una tabla de fechas oculta que me permite ver los datos por año y mes. De hecho, eliminé los niveles de Trimestre y Día en este objeto visual y luego desglosé hasta Año Mes.

image_thumb-10-8242464

El Visual 2 en el informe original anterior es la misma información pero sin usar la jerarquía de fechas, pero usando las fechas reales de las transacciones (fecha de contabilización en realidad). También creé un par de tarjetas (3 y 4 arriba) y las usé para encontrar la primera fecha y la última fecha en el período seleccionado. Eso me permitió hacer clic en cada mes y comprobar que la primera y la última fecha de ese mes parecían estar completas. El simple uso de este informe de auditoría me permitió encontrar algunos datos faltantes en mis extractos de csv antes de comenzar el ejercicio.

Días entre dos transacciones

Bien, el siguiente trabajo fue filtrar por mi peluquero y mirar las transacciones en una tabla. Puede ver una segmentación a la izquierda a continuación y la tabla de datos a la derecha. Mmmm, ¡parece que mi peluquero subió el precio un 3% en abril de este año!

image_thumb-11-3028859

La tarea en cuestión es averiguar el número de días (o semanas) entre cada una de estas transacciones. Esto es fácil en Excel porque una hoja de cálculo tiene una referencia de celda y puede copiar fórmulas y mantener la relación relativa entre las celdas. Esto no es posible en Power BI, Power Pivot o cualquier otra base de datos. Las bases de datos no almacenan datos utilizando referencias de ubicación absolutas; simplemente almacenan los datos de la manera más eficiente posible y el orden no es referenciable (a menos que tenga un ID de fila, y eso no es una buena idea en Power BI).

Días desde la transacción anterior

Como es mi estilo, en lugar de que yo les dé la respuesta, quiero mostrarles cómo resolví el problema. Siempre es mejor configurar una mesa (o matriz) y resolver cada parte del rompecabezas paso a paso. TODOS hacen esto en Excel automáticamente, pero no es tan intuitivo en Power BI. SIEMPRE debe resolver los problemas de esta manera en Power BI.

Extraiga la fecha correcta de ESTA transacción

La primera tarea es poder acceder a la fecha de la transacción actual. Para esta fórmula, decidí usar la sintaxis VAR. Entonces, mi primera medida WIP fue la siguiente:

WIP = VAR ThisTranDate = SELECTEDVALUE('Visa Extracts'[Date])
      Return ThisTranDate

Cuando coloqué esta medida en mi tabla, esto es lo que obtuve: confirmación de que la medida identifica correctamente la fecha de transacción actual.

image_thumb-12-4392491

Fecha de la transacción anterior

Hay 2 beneficios de crear una tabla y colocar su medida WIP en esa tabla. En primer lugar, puede ver si la medida le está dando la respuesta que espera y, en segundo lugar, poder «ver» el problema frente a usted lo ayuda a pensar en los siguientes pasos. Como dije anteriormente, no es posible usar una referencia de celda en Power BI. La única forma de resolver problemas como este en DAX es filtrar primero los datos y luego actuar sobre los datos que no se han filtrado (Filtrar primero, evaluar segundo). Si observa la tabla anterior, “verá” que la forma de resolver este problema es filtrar todos los registros que ocurren en o después de la fecha de transacción actual y luego calcular la última fecha.

Nota: Esto funciona porque sé que no tengo más de 1 transacción para mi peluquero el mismo día. Si hubo más de una transacción el mismo día, necesitaría alguna forma de determinar el orden de las transacciones dentro del mismo día, como una marca de tiempo o un ID ordinal de algún tipo.

Entonces, mi trabajo para encontrar la transacción anterior es simplemente usar FILTER para encontrar el último registro DESPUÉS de filtrar las fechas de transacciones actuales y futuras. Modifiqué mi fórmula WIP como se muestra a continuación (hay otras fórmulas válidas que también funcionarán). Observe cómo la función FILTRO primero elimina todas las transacciones en o después de la transacción actual, luego devuelve la última fecha que queda en la lista, que resulta ser la fecha de la transacción anterior.

image_thumb-13-5491360

Días entre transacciones

Ahora que tenía las fechas actual y anterior, pude encontrar los días entre cada transacción. Aquí está mi fórmula:

Weeks Between Transactions = 
      VAR ThisTranDate = SELECTEDVALUE('Visa Extracts'[Date])
      VAR PrevDate = CALCULATE(
                        MAX('Visa Extracts'[Date]),
                        FILTER(ALL('Visa Extracts'[Date]),'Visa Extracts'[Date] < ThisTranDate)
                     )
      Return DATEDIFF(PrevDate,ThisTranDate,DAY)/7

Como puede ver a continuación, esta fórmula me da las semanas entre cada transacción, pero no me da un promedio general.

image_thumb-14-3352813

Días promedio entre transacciones

Aquí es donde entra la experiencia. Supe de inmediato que la forma de averiguar el promedio de todas las transacciones seleccionadas era escribir una función AVEARGEX que itera sobre las fechas en el visual de la Tabla para crear la respuesta. En otras palabras, necesitaba escribir una medida que «simulara» el papel de la tabla anterior y trabajar en cada transacción una a la vez, luego calcular el promedio general. Hablo de este concepto de «simular» en profundidad en mi artículo aquí. Debido a la forma en que funciona el contexto del filtro (el filtro que proviene del elemento visual de la tabla), resulta que esta nueva medida AVERAGEX funcionará igualmente bien para cada fila de la tabla, así como para el total de la tabla.

image_thumb-15-5910335

Como resultado, eliminé mi primera medida y me quedé con lo siguiente:

image_thumb-16-7430476

super-charge-power-bi-ad_1-1024x128-6595595

Envolver

Cualquier análisis es tan bueno como los datos que haya cargado. De hecho, me sorprendería si pasara 13 semanas sin un corte de pelo y pensara que podría haber pagado mi corte de pelo de octubre de 2017 en efectivo (raro, pero posible). Resulta que, si supongo que falta un corte de pelo entre julio y octubre, mi promedio es de 6,33 semanas. Calculé eso usando esta fórmula.

Avg Weeks Between Transactions =
    SUMX(VALUES('Visa Extracts'[Date]), [Weeks Between Transactions])
    / 
    (DISTINCTCOUNT('Visa Extracts'[Date]) + 1)

Preguntas abiertas inquietantes

Este artículo crea una gran cantidad de preguntas inquietantes que están fuera del alcance de este artículo, como «¿R Marketing realmente gasta más $ 30 por un corte de pelo» y «Qué sucedió a principios de junio de 2017 que provocó un corte de pelo después de 4 semanas»?

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.