Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Tengo las siguientes tablas en Power BI Desktop:
Para cada cliente, me gustaría agrupar su facturación en 3 categorías: (1) Nuevo, (2) Churn y (3) Existente
Mi objetivo es calcular el cambio mes a mes en Facturación $ para cada cliente y aislarlo para cada categoría: Nuevo, Existente y Churn. A continuación, me gustaría ver esto por mes que agrega cada número de cliente para cada mes.
Si el cliente no tiene una fecha de aviso de cancelación, solo tendría dos categorías: Nuevo y Existente.
No estoy seguro de cómo manejar esto si agregar algunas columnas calculadas y / o hacerlo con medidas.
Tengo la parte fácil, que calcula el cambio mes a mes:
Facturación del mes actual: suma (facturación[Facturado])
Facturación del mes anterior: calcular ( [Facturación del mes actual], DATEADD('Fecha'[Fecha], -1, MES))
Cambio de moM á [Facturación del mes actual] - [Facturación del mes anterior]
He abordado los siguientes pasos, pero no obteniendo los resultados que necesito (debajo de la tabla). ¿Cuál es la mejor manera de manejar esto? Me gustaría poder profundizar en el nivel del cliente y mirarlo por producto también.
Mes | MoM Nuevo | MoM Existente | MoM Churn |
Enero | |||
Febrero | |||
Marzo | |||
Abril | |||
Mayo | |||
Junio | |||
Julio | |||
Agosto |
Solved! Go to Solution.
Hola
Si lo hago bien, su cliente tiene un estado para cada pedido que realice, basado en la fecha de facturación,su primera fecha de compra y una fecha de cancelacióneventual. Este estado puede ser nuevo/existente/churn y puede evolucionar con el tiempo para un cliente.
En este caso, iría con una columna calculada. Pero este escenario también podría resolverse utilizando varias medidas dax o incluso otras técnicas. Puede encontrar el archivo .pbix que contiene lo que he hecho.
¿Por qué iría por una columna calculada?
Aquí, ya que su estado depende de la fecha del pedido, lo agregaría a la tabla facturación del cliente, ya que es la única tabla que contiene este nivel de detalle.
El código para 'Facturación del cliente'[Estado del cliente]
Customer Status =
VAR OrderDate = Sales[Order Date]
VAR FirstBillingDate = RELATED( Customer[First Billing Date] )
VAR CancellationDate = RELATED( Customer[Cancellation Notice Date] )
VAR CustomerCategory =
SWITCH(
TRUE(),
OrderDate >= CancellationDate, "Churn",
OrderDate <= (FirstBillingDate + 120), "New",
"Existing"
)
RETURN CustomerCategory
Espero haber reproducido su lógica de negocios en el SWITCH como pretendía.
A continuación, simplemente tiene que agregar su campo a su visual y su medida moM que ya ha creado y obtiene:
Pero, ¿son correctos los resultados? Hice una comprobación rápida de spot mirando los detalles del cliente:
En julio, nuestro cliente gasta 1222 y se considera existente (primera facturación el 22 de febrero)
A continuación, en agosto, el cliente sigue siendo "existente", pero gasta sólo 1100, que es de hecho 122 menos que el 1222 de julio
Si no puede o no desea usar una columna calculada, todavía puede abordar el escenario con medidas DAX. Una de las principales desventajas es que tendrás que escribir cada medida tres veces: una para la nueva, otra para la existente y otra para el churn.
Las medidas para Sales for New serían las siguientes:
Sales for New =
VAR SalesLinesMadeByNew =
FILTER(
SUMMARIZE(Sales, Sales[Order Date] , Sales[CustomerKey], Customer[First Billing Date] ),
Sales[Order Date] <= Customer[First Billing Date] + 120
)
VAR SalesAmountMadeByNew =
CALCULATE(
[Sales Amount],
SalesLinesMadeByNew
)
Return SalesAmountMadeByNew
Aquí primero calcule una tabla que luego usaré como filtro.
Esta tabla se almacena en la variable SalesLinesMadeByNew y contiene todos los valores de Customer/OrderDate que se consideran New.
Tenga en cuenta que seguramente hay otras alternativas de DAX para realizar este cálculo.
Y se puede ver que la opción 2 produce los mismos resultados que la opción 1:
He añadido la columna calculada como un encabezado de columna y luego las diferentes medidas.
¿Cuáles son las diferencias de rendimiento?
En mi máquina, la consulta para generar el año/mes del informe con Sales + Sales MoM para cada estado del cliente:
La opción 1 es 4 veces más rápida que la opción 2
¿Qué hago si mis parámetros tienen que cambiar? Por ejemplo, necesito dejar que el usuario decida qué duración define la "Nueva categoría" con una segmentación de datos (en lugar de usar siempre 120 días).
Entonces la opción 1 ya no funciona y usar medidas DAX con un parámetro sería la mejor opción.
Saludos
Thomas
Hola
Comparte el enlace desde donde puedo descargar tu archivo PBI.
Aquíestá el enlace al archivo pbix.
Gracias por su ayuda. Usé @ThomasKwakman 's Option #1 como la solución y se ve muy bien, pero sólo tengo este problema para resolver. Creo que el problema es con la medida Mes a mes donde tomar la facturación del mes actual menos la facturación del mes anterior. Sin embargo, esto hace que el mes (cuando se produce la cancelación) tenga los períodos "Existente" y "Churn" y uno falta Facturación actual y el otro falta Facturación del mes anterior. Verás en el archivo pbix de lo que estoy hablando.
Me gustaría que el Estado del Cliente fuera mutuamente excluyente, es decir, un mes determinado debe tener uno de los estados, no ambos.
Hola @pc2 ,
No sé qué pasó, pero mi respuesta se publicó en el foro español, pero no aquí...
Sí, como ha notado que un cliente puede tener un estado diferente para un mismo mes y, como entiendo, solo desea un valor.
Así que aquí tenemos dos cosas a tener en cuenta:
- el primero para el estado de abandono, ya que la fecha de cancelación puede ocurrir durante un mes (podríamos tener simultáneamente existente y Churn)
- el segundo para el nuevo estado, ya que el final del nuevo período puede ocurrir durante un mes (podríamos tener simultáneamente Nuevo y Existente)
Para ello es necesario cambiar el código de la columna calculada. La lógica de negocios será:
Para la rotación,ya que la fecha de cancelación puede producirse durante el mes, debe convertirla al inicio o al final del mes.
Fui por fin de mes
por ejemplo, 15-oct-2020 se convertirá a 01-oct-2020 y todas las ventas en octubre se considerarán como
Para nuevo, no es la primera fecha de facturación que vamos a cambiar, pero la primera fecha de facturación + 120 días que vamos a cambiar.
Usé la siguiente lógica:
Aquí está el código:
Customer Status =
VAR OrderDate = Sales[Order Date]
// rounding end of "New status" period at start or end of month, to get all sales of one month under one status
VAR FirstBillingDate = RELATED( Customer[First Billing Date] )
VAR EndOfNewPeriodRaw = FirstBillingDate + 120
VAR EndOfNewPeriodStartOfMonth =
IF( DAY(FirstBillingDate) <= 15,
DATE( YEAR(EndOfNewPeriodRaw), MONTH(EndOfNewPeriodRaw), 1),
EOMONTH(EndOfNewPeriodRaw,0)+1
)
// same here, rounding cancellation date to start of month
VAR CancellationDateRaw = RELATED( Customer[Cancellation Notice Date] )
VAR CancellationDateStartOfMonth = DATE( YEAR( CancellationDateRaw ) , MONTH( CancellationDateRaw ) , 1 )
// calculating status
VAR CustomerCategory =
SWITCH(
TRUE(),
OrderDate >= CancellationDateStartOfMonth, "Churn",
OrderDate < EndOfNewPeriodStartOfMonth, "New",
"Existing"
)
RETURN CustomerCategory
Nota: aquí tomé algunas decisiones con respecto a la lógica de negocios, depende de usted para cambiarlo si es necesario
Nota 2: aquí si usted absolutamente necesita tener 120 días para el nuevo período, entonces usted tendrá que aceptar tener posiblemente los dos estados diferentes "existente" y "nuevo" para un mes para un cliente
¿Cómo puedes comprobar que todo funciona bien?
1. Cree una medida que cuente el número de estados de cliente distintos: DISTINCTCOUNT( ' Customer Billings'[Customer Status] )
2. Añadirlo a un objeto visual con la dimensionalidad del cliente/año/mes
3. Añadir un filtro en la medida: mayor que 1
•> Visual debe estar vacío, ya que no desea que ningún cliente con más de un estado de cliente durante un mes
Espero que ayude y funcione. ¿De verdad?
Que tengas un buen día
Thomas
Muchas gracias. He actualizado y tengo el siguiente resultado a continuación.
Puse el archivo pbix aquí (enlace de archivo). ¿Le importaría echarle un vistazo, por favor?
He actualizado la columna calculada, Estado del cliente, como usted prescribió y puedo ver que cada mes tiene un estado único ahora.
Sin embargo, el cálculo M2M todavía está desactivado. Verá que para el Cliente B, para el mes de junio de 2019, sucede lo siguiente:
Creo que el problema es con el cálculo M2M para el mes superpuesto en cuestión.
Lo que esperaba ver está a continuación (lo me burlé en Excel):
Hola @p2c,
Tuve un vistazo de cerca a sus capturas de pantalla y gracias por ello, muy claro.
Después de pensar un poco, no estoy seguro de que el MoM calcula la figura incorrecta. En realidad, realmente depende de lo que queremos que calcule.
Si nos fijamos en el nivel de cliente,por lo que en las 3 columnas en el extremo derecho,vemos que el total calculado sigue siendo correcto:
Si procesamos la forma que pretendía en su maqueta, significará que su nueva categoría agregará ventas para que los clientes se conviertan en nuevas, pero no subsaplará las ventas para los clientes que abandonan la categoría. Lo que producirá resultados demasiado altos.
Aquí hay un ejemplo,digamos que miramos lo que sucedió entre febrero y marzo para la nueva categoría:
> Resultado: la nueva categoría creció por 10 100$ en marzo. Tenemos cada vez más nuevos clientes... cuando en realidad no lo hacemos como lo hicimos -500$
Tal vez estoy leyendo toda la situación desde el ángulo equivocado, por favor dime cuál es tu pensamiento en este caso.
Mejor
Thomas
Gracias por su respuesta. Te escucho que el Total M2M es correcto y estoy de acuerdo. Pero, creo que la lógica en M2M necesita cambiar para conseguir lo que estoy detrás de Thomas. Por favor, vea la imagen de abajo.
¿Hay alguna manera de actualizar DAX para calcular M2M para que el número de Prev Mo se incluya en el estado correcto?
Para junio de 2019, este es el estado "Existente" y quiero que tanto Current como Prev Mo Billing aparezcan aquí.
Lo mismo para junio de 2020, este es el estado "Churn" y quiero que tanto Current como Prev Mo Billing aparezcan aquí.
Thomas, creo que he resuelto esto. Estoy haciendo algunas pruebas para asegurarme de que esto es correcto. Te lo haré saber.
He añadido otra columna calculada para agarrar Prev Mo Billing. Entonces, en lugar de usar la inteligencia de tiempo para calcular la facturación previa, estoy sumando esta nueva columna para obtener el Prev Mo Biling, que luego se utiliza para calcular M2M. Parece que me da lo que quiero (ver 2a imagen a continuación).
¿Puedes echar un vistazo y ver si lo que hice tiene sentido y debería funcionar en todo momento?
Thomas, mi sugerencia anterior tiene un defecto. Si el cliente no compra un producto específico en un mes posterior, no se capturará prev Mo Billing.
Por ejemplo, si el cliente compra
La columna calculada de Prev Mo Billing para Mar 2020 solo recogerá para el Producto 1 y el Producto 2 será cero y esto debería ser $50. Esto se debe a que no hay línea producto 2 en marzo de 2020. 😞
¿Alguna idea de cómo solucionar esto?
Tuve una solución y funciona. He creado otra tabla de facturación con todas las fechas en ella, por lo que puedo calcular los números M2M correctamente.
New Billing Table = CROSSJOIN( SUMMARIZE('Date', 'Date'[Date]), VALUES(Billing[Product]), SUMMARIZE( Billing, Billing[Customer_ID], Billing[Customer_Name]))
Luego agregué dos columnas calculadas para Billing y Prev Mo Billing.
Esto funciona, pero realmente puso en globo el tamaño del archivo, pero no veo ninguna otra manera de hacerlo para este problema que estaba resolviendo.
Tu solución y tus códigos de detalle realmente me ayudaron. Estoy marcando la tuya como la solución proporcionada.
Gracias de nuevo por su ayuda.
Hola @pc2
El comportamiento que observó y describió va en línea con el código, pero según entiendo no es el comportamiento que esperaba.
Aquí, a medida que la fecha de cancelación se produce durante el mes, su cliente puede tener dos estados y el MoM procesará un valor para cada uno de ellos. En el valor total se ve bien, sin embargo, tener dos estados con un valor cada uno puede crear confusión.
La lógica de negocios debe cambiar y solo desea un estado al mes. La solución sigue siendo la misma, pero los valores de la columna calculada necesitan una pequeña corrección:
Aquí está el nuevo código para 'Facturaciones de cliente'[Estado del cliente] :
Customer Status =
VAR OrderDate = Sales[Order Date]
// rounding end of "New status" period at start or end of month, to get all sales of one month under one status
VAR FirstBillingDate = RELATED( Customer[First Billing Date] )
VAR EndOfNewPeriodRaw = FirstBillingDate + 120
VAR EndOfNewPeriodStartOfMonth =
IF( DAY(FirstBillingDate) <= 15,
DATE( YEAR(EndOfNewPeriodRaw), MONTH(EndOfNewPeriodRaw), 1),
EOMONTH(EndOfNewPeriodRaw,0)+1
)
// same here, rounding cancellation date to start of month
VAR CancellationDateRaw = RELATED( Customer[Cancellation Notice Date] )
VAR CancellationDateStartOfMonth = DATE( YEAR( CancellationDateRaw ) , MONTH( CancellationDateRaw ) , 1 )
// calculating status
VAR CustomerStatus =
SWITCH(
TRUE(),
OrderDate >= CancellationDateStartOfMonth, "Churn",
OrderDate < EndOfNewPeriodStartOfMonth, "New",
"Existing"
)
RETURN CustomerStatus
Nota 1: si desea aplicar un estado por mes para un cliente, la regla de 120 días será un poco alterada: a veces será 115, 120, 125, ... Si necesita aplicar la regla de 120 días después de la primera fecha de facturación, tendrá que aceptar que para algún cliente tendrá los estados "Nuevo" y "Existente" para el mismo mes.
Nota 2: por supuesto, aquí tomé decisiones con respecto a la lógica de negocios, pero sólo tiene que adaptarlo a lo que mejor se adapte a su caso
Para comprobar si se valida la regla "un estado por cliente-mes", puede:
1. Cree la siguiente medida DAX
Nb of distinct statuses = DISTINCTCOUNT( Sales[Customer Status] )
2. Arrástrelo a una tabla con una dimensionalidad de cliente/año/mes
3. Aplique un filtro en la medida para comprobar si hay alguna celda con un valor superior a 1. La tabla debe estar vacía.
Espero que funcione para ti. ¿De verdad?
Que tengas un buen día
Thomas
Hola
Si lo hago bien, su cliente tiene un estado para cada pedido que realice, basado en la fecha de facturación,su primera fecha de compra y una fecha de cancelacióneventual. Este estado puede ser nuevo/existente/churn y puede evolucionar con el tiempo para un cliente.
En este caso, iría con una columna calculada. Pero este escenario también podría resolverse utilizando varias medidas dax o incluso otras técnicas. Puede encontrar el archivo .pbix que contiene lo que he hecho.
¿Por qué iría por una columna calculada?
Aquí, ya que su estado depende de la fecha del pedido, lo agregaría a la tabla facturación del cliente, ya que es la única tabla que contiene este nivel de detalle.
El código para 'Facturación del cliente'[Estado del cliente]
Customer Status =
VAR OrderDate = Sales[Order Date]
VAR FirstBillingDate = RELATED( Customer[First Billing Date] )
VAR CancellationDate = RELATED( Customer[Cancellation Notice Date] )
VAR CustomerCategory =
SWITCH(
TRUE(),
OrderDate >= CancellationDate, "Churn",
OrderDate <= (FirstBillingDate + 120), "New",
"Existing"
)
RETURN CustomerCategory
Espero haber reproducido su lógica de negocios en el SWITCH como pretendía.
A continuación, simplemente tiene que agregar su campo a su visual y su medida moM que ya ha creado y obtiene:
Pero, ¿son correctos los resultados? Hice una comprobación rápida de spot mirando los detalles del cliente:
En julio, nuestro cliente gasta 1222 y se considera existente (primera facturación el 22 de febrero)
A continuación, en agosto, el cliente sigue siendo "existente", pero gasta sólo 1100, que es de hecho 122 menos que el 1222 de julio
Si no puede o no desea usar una columna calculada, todavía puede abordar el escenario con medidas DAX. Una de las principales desventajas es que tendrás que escribir cada medida tres veces: una para la nueva, otra para la existente y otra para el churn.
Las medidas para Sales for New serían las siguientes:
Sales for New =
VAR SalesLinesMadeByNew =
FILTER(
SUMMARIZE(Sales, Sales[Order Date] , Sales[CustomerKey], Customer[First Billing Date] ),
Sales[Order Date] <= Customer[First Billing Date] + 120
)
VAR SalesAmountMadeByNew =
CALCULATE(
[Sales Amount],
SalesLinesMadeByNew
)
Return SalesAmountMadeByNew
Aquí primero calcule una tabla que luego usaré como filtro.
Esta tabla se almacena en la variable SalesLinesMadeByNew y contiene todos los valores de Customer/OrderDate que se consideran New.
Tenga en cuenta que seguramente hay otras alternativas de DAX para realizar este cálculo.
Y se puede ver que la opción 2 produce los mismos resultados que la opción 1:
He añadido la columna calculada como un encabezado de columna y luego las diferentes medidas.
¿Cuáles son las diferencias de rendimiento?
En mi máquina, la consulta para generar el año/mes del informe con Sales + Sales MoM para cada estado del cliente:
La opción 1 es 4 veces más rápida que la opción 2
¿Qué hago si mis parámetros tienen que cambiar? Por ejemplo, necesito dejar que el usuario decida qué duración define la "Nueva categoría" con una segmentación de datos (en lugar de usar siempre 120 días).
Entonces la opción 1 ya no funciona y usar medidas DAX con un parámetro sería la mejor opción.
Saludos
Thomas
Muchas gracias su respuesta detallada.
Intenté Opción #1, Columna calculada. Casi está produciendo el resultado correcto, pero hay un problema. He incluido la foto de abajo.
He aislado para un solo cliente a cero en el problema
Este cliente tiene una fecha de cancelación del 6/22/2020.
Como puede ver, para el mes de junio de 2020, el importe de M2M aparece en "Churn" y "Existing". El importe neto está bien, ya que se compensa, pero individualmente, causa exageración en uno y subestimación en el otro.
¿Cómo puedo solucionar este problema?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |