Clientes perdidos por semana/mes/trimestre

[Syndicate_Admin]

Hola a todos,

Soy un poco nuevo en powerbi, y actualmente estoy usando powerbi desktop con licencia profesional.

Tengo algunos datos de venta al por menor que tienen las siguientes columnas:

fecha

client_id

product_category

artículo

precio

Actualmente hago algunos cálculos en SQL para tener los siguientes dos campos:

1. Número de clientes perdidos por semana, por mes y por trimestre:
   • Cuando un cliente realizó una o más compras en cada una de las 3 semanas anteriores y no compró en la semana actual:
   • es decir, Anthony compró 3 artículos en la semana 2 y compró 1 artículo en la semana 3 y compró 2 artículos en la semana 4 pero no compró nada en la semana 5, por lo que Anthony se cuenta como un cliente perdido en la semana 5
     
     
2. Número de clientes devueltos por semana, por mes y por trimestre:
   • Cuando un cliente compra uno o más artículos en la semana -3 y en la semana -2, luego no realizó ninguna compra en la semana -1 o la semana anterior) luego realizó una compra en la semana actual:
   • es decir, Anthony compró 3 artículos en la semana 7 y compró 2 artículos en la semana 8, luego no compró nada en la semana 9, luego compró 4 artículos en la semana 10; por lo que Anthony se cuenta como un cliente devuelto en la semana 10

¿Hay alguna manera de hacer estos cálculos en una medida en Power BI en lugar de hacerlo a nivel de datos en SQL?

El marco temporal de los datos que tengo es de los últimos 5 años, desde el 01-01-2020 hasta hoy, y mi informe debería mostrar solo datos hasta la semana pasada, el mes pasado y el trimestre pasado

[Syndicate_Admin · Accepted solution]

Aquí está la solución para la semana:

lost_by_week = 
VAR CurrentDate = MAX('Calendar'[Date])  -- You need a reference point
VAR Current_Week_Start = 
    CurrentDate - MOD(WEEKDAY(CurrentDate, 2) - 6, 7)  -- Saturday as week start

VAR Last_Week_Start = Current_Week_Start - 7

-- Get clients active last week
VAR Last_Week = 
    CALCULATETABLE(
        VALUES(data_table[client_id]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] >= Last_Week_Start &&
            'Calendar'[Date] < Last_Week_Start + 7
        )
    )

-- Get clients active this week
VAR Current_Week = 
    CALCULATETABLE(
        VALUES(data_table[client_id]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] >= Current_Week_Start &&
            'Calendar'[Date] < Current_Week_Start + 7
        )
    )

-- Clients who were in last week but not this week
VAR LostClients = EXCEPT(Last_Week, Current_Week)

RETURN COUNTROWS(LostClients)

[Syndicate_Admin]

Pude encontrar una solución para el mensual y el trimestral, pero en cuanto al semanal, me enfrento a un desafío, no hay ninguna función que pueda devolver el primer día de la semana ya que en mi semana no comienza el lunes o el domingo sino un viernes, y en cierta medida no puedo asignar un VAR para tomar la fecha actual y usar un SWITCH para verificar qué día es es para hacer que el primer día de la semana siguiente al día Nombre,

A continuación se muestra el código para el mensual y trimestral y después el código con el que todavía estoy atrapado para el semanal:

lost_by_month = 

VAR Last_Month_Date = DATEADD('Calendar'[Date],-1,MONTH)

VAR Last_Month = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                               'Calendar'[Date], 
                                STARTOFMONTH(Last_Month_Date), 
                                1,
                                MONTH 
                            )
                )

VAR Current_Month = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                               'Calendar'[Date], 
                                STARTOFMONTH('Calendar'[Date]), 
                                1,
                                MONTH 
                            )
                )

VAR tb_unique = DISTINCT(EXCEPT(Last_Month, Current_Month))

RETURN COUNTROWS(tb_unique)


lost_by_quarter = 

VAR Last_Quarter_Date = DATEADD('Calendar'[Date],-1,QUARTER)

VAR Last_Quarter = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                               'Calendar'[Date], 
                                STARTOFQUARTER(Last_Quarter_Date), 
                                1,
                                QUARTER 
                            )
                )

VAR Current_Quqrter = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                               'Calendar'[Date], 
                                STARTOFQUARTER('Calendar'[Date]), 
                                1,
                                QUARTER 
                            )
                )

VAR tb_unique = DISTINCT(EXCEPT(Last_Quarter,Current_Quqrter))


RETURN COUNTROWS(tb_unique)

Y aquí está el código con el que estoy atrapado para el semanario:

lost_by_week = 

VAR Last_Week_Date = DATEADD('Calendar'[Date],-6,DAY)

VAR Last_Week_Day_Nb = WEEKDAY(Last_Week_Date, 1)

VAR Day_Of_Week = SWITCH( 
                    TRUE(), 0
                    Last_Week_Day_Nb = 1, 2,
                    Last_Week_Day_Nb = 2, 3,
                    Last_Week_Day_Nb = 3, 4,
                    Last_Week_Day_Nb = 4, 5,
                    Last_Week_Day_Nb = 5, 6,
                    Last_Week_Day_Nb = 6, 0,
                    1)

VAR Last_Week_First_Day = CALCULATE(Last_Week_Date - Day_Of_Week)


VAR Current_Week_Day_Nb = WEEKDAY(DATEADD('Calendar'[Date],0,DAY), 1)

VAR Day_Of_Week = SWITCH( 
                    TRUE(), 
                    Current_Week_Day_Nb = 1, 2,
                    Current_Week_Day_Nb = 2, 3,
                    Current_Week_Day_Nb = 3, 4,
                    Current_Week_Day_Nb = 4, 5,
                    Current_Week_Day_Nb = 5, 6,
                    Current_Week_Day_Nb = 6, 0,
                    1)

VAR Current_Week_First_Day = Current_Week_Day_Nb - Day_Of_Week


VAR Last_Week = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                               'Calendar'[Date], 
                                Last_Week_First_Day, 
                                6,
                                DAY 
                            )
                )

VAR Current_Week = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                               'Calendar'[Date],
                                Current_Week_First_Day, 
                                6,
                                DAY 
                            )
                )

VAR tb_unique = DISTINCT(EXCEPT(Last_Week, Current_Week))

RETURN COUNTROWS(tb_unique)

[Syndicate_Admin]

@burakkaragoz

Esto es lo que pude hacer hasta ahora:

encontrar los clientes perdidos mensuales en febrero de 2024 utilizando lo siguiente:

lost_clients = 

VAR jan_clients = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                               data_table[date], 
                                DATE(2024,1,1),
                                DAY(EOMONTH(DATE(2024,1,1),0)),
                                DAY 
                            )
                )

VAR feb_clients = CALCULATETABLE(
                SUMMARIZE(
                            data_table, 
                            data_table[client_id]
                        ), 
                 DATESINPERIOD(
                                data_table[date], 
                                DATE(2024,2,1),
                                DAY(EOMONTH(DATE(2024,2,1),0)),
                                DAY  
                            )
                )

VAR tb_unique = DISTINCT(EXCEPT(jan_clients,feb_clients))

VAR counting = COUNTROWS(tb_unique)

RETURN counting

[Syndicate_Admin]

@anthony5 ,

¡Gran punto! Tienes toda la razón: un cliente solo debe considerarse "perdido" una vez, en el momento en que deja de interactuar, no repetidamente cada mes a partir de entonces.

Para mejorar la lógica, puede probar el siguiente enfoque en DAX:

1. Identifique la última fecha de transacción para cada cliente.
2. Compare esa fecha con el período actual de la segmentación.
3. Marque al cliente como "Perdido" solo en el primer período después de su última actividad, y no en los meses siguientes.

He aquí un esquema conceptual:

LastPurchaseDate = 
CALCULATE(
    MAX(Sales[Date]),
    ALLEXCEPT(Sales, Sales[ClientID])
)

IsLost = 
IF(
    Sales[Date] = 
        CALCULATE(
            MIN(Sales[Date]),
            FILTER(
                Sales,
                Sales[ClientID] = EARLIER(Sales[ClientID]) &&
                Sales[Date] > [LastPurchaseDate]
            )
        ),
    "Lost",
    BLANK()
)

De esta manera, cada cliente solo aparecerá como "Perdido" una vez, en el primer período después de su última compra, y no en todos los períodos posteriores.

¡Hazme saber si necesitas ayuda para adaptar esto a tu modelo de datos específico o configuración visual!
traducción y formato respaldados por IA

[Syndicate_Admin]

Hola de nuevo @burakkaragoz

Estoy tratando de usar su código para hacer lo que necesito, pero no funciona,

Permítanme mostrarles en una captura de pantalla el resultado que estoy tratando de hacer

en mi página de informe creé un nuevo parámetro Campos que lee de la tabla "Calendario" los siguientes tres campos: Semana, Mes, Trimestre

A continuación, la imagen principal es una matriz que muestra:

• La lista de categorías de productos como filas
• Las columnas serán Nb de semana o Nb de mes o Número de trimestre
• Los valores son el número de clientes perdidos y el número de clientes que regresan

Estaba tratando de adjuntar el archivo Power BI aquí, pero no pude encontrar ningún archivo adjunto, así que aquí está en este enlace:

https://github.com/antonynasr5/powerbi

[Syndicate_Admin]

@burakkaragoz

Muy bien, veré cómo puedo usar lo que me proporcionaste y diré lo que encuentre

[Syndicate_Admin]

Siéntase libre de adaptar la lógica para que se ajuste a la estructura de su modelo, especialmente si está trabajando con una tabla de fechas o utilizando funciones de inteligencia de tiempo como DATESINPERIOD o SAMEPERIODLASTYEAR. Si tiene algún problema con la función EARLY() o necesita optimizar el rendimiento en grandes conjuntos de datos, también podemos explorar alternativas utilizando patrones VAR y GROUPBY.

Espero escuchar cómo funciona en su escenario. ¡Feliz de ayudar a refinarlo aún más una vez que lo hayas probado!

[Syndicate_Admin]

Hola @anthony5 ,

Sí, puede calcular los clientes perdidos y devueltos mediante medidas de DAX en Power BI. Deberá crear tablas calculadas o usar funciones de inteligencia de tiempo para realizar un seguimiento de la actividad del cliente a lo largo de semanas, meses y trimestres. Con las funciones CALCULATE, FILTER, DATESINPERIOD y NOT, puede definir la lógica para identificar a los clientes que cumplen los criterios perdidos o devueltos. Avísame si quieres que te ayudemos a escribir las fórmulas exactas del DAX.
traducción y formato respaldados por IA

[Syndicate_Admin]

Hola @burakkaragoz

Gracias por la explicación que nos ha dado.

Déjame tratar de encontrarlo yo mismo usando la lógica que mencionaste, y lo publicaré aquí y luego me dirás si es cierto o no y me proporcionarás las fórmulas

[Syndicate_Admin]

¡Excelente! Espero ver su acercamiento. Una vez que publique su solución, estaré encantado de revisarla y ayudar a refinar las fórmulas de DAX si es necesario. ¡Buena suerte!