Dynamic counting of lost customers (customer Churn)

[Stefan_38]

Hi community,

I am working on a measure that is able to calculate a dynamic customer churn, so I can visualize the trend of lost customers over time. The way a lost customer is defined is if they purchased any unit in the month n- 1 and not in month n. 

I have made the two below measures and they work fine for the current month but it doesn't work as intended looking back as, I believe that the MAX function, ensures that if all customers that made a unit purchase in Month X will not be counted as lost for all the month in the past. How do I modify the below so I can create a dynamic customer churn trend?

Thank you in advance

Date Lost Customer 1m =

CALCULATE (                                  

    EOMONTH ( MAX ( Financials[Date Key] ), 1 ),

    REMOVEFILTERS ( Dim_Calendar )                

)

# Lost Customers 1m =

VAR LastDateLost =

    CALCULATE ( MAX ( Dim_Calendar[Date key] ), ALLSELECTED ( Dim_Calendar ) )

VAR CustomersWithLostDate =

    CALCULATETABLE (

        ADDCOLUMNS (

            VALUES ( Financials[Account key] ),

            "@LostCustomerDate", [Date Lost Customer 1m],

            "Units",

                CALCULATE (

                    SUM ( Financials[Value] ),

                    Financials[Line item] = "Units"

                )

        ),

        Dim_Calendar[Date key] <= LastDateLost

    )

VAR LostCustomers =

        FILTER (

            CustomersWithLostDate,

            [@LostCustomerDate]

                IN VALUES ( Dim_Calendar[Date key] )

        )

VAR Result =

    COUNTROWS ( LostCustomers )

RETURN

    Result

[sjoerdvn · Accepted solution]

try this:

# Lost customers 1m = 
--  date start selected month
VAR dssm = CALCULATE(STARTOFMONTH(Dim_Calendar[Date key]), Dim_Calendar[Date key]= MAX(Dim_Calendar[Date key]))
-- date end selected month
VAR desm= ENDOFMONTH(Dim_Calendar[Date key])
-- dates selected month
VAR dsm = DATESBETWEEN(Dim_Calendar[Date key], dssm, desm)
-- dates previous month
VAR dpm = PREVIOUSMONTH(dsm)
-- customers selected month
VAR csm = CALCULATETABLE(VALUES(Financials[Account key])
	, ALL( Dim_Calendar), dsm
	, Financials[Line item] = "Units")
-- customers previous month
VAR cpm = CALCULATETABLE(VALUES(Financials[Account key])
	, ALL( Dim_Calendar), dpm
	, Financials[Line item] = "Units")
-- count the customers in the previos month that are not in the selected month
RETURN COUNTROWS(EXCEPT(cpm,csm))

[Stefan_38]

This one worked very well. Thank you.

[123abc]

Calculate the Last Purchase Date for Each Customer

Last Purchase Date =
CALCULATE(
MAX(Financials[Date Key]),
ALLEXCEPT(Financials, Financials[Account key]),
Financials[Line item] = "Units"
)

2. Calculate the Number of Lost Customers

# Lost Customers 1m =
VAR CurrentDate = MAX(Dim_Calendar[Date key])
VAR LastPurchaseDates =
CALCULATETABLE(
SUMMARIZE(
Financials,
Financials[Account key],
"Last Purchase Date", [Last Purchase Date]
),
Financials[Date Key] <= CurrentDate
)
VAR LostCustomers =
COUNTROWS(
FILTER(
LastPurchaseDates,
[Last Purchase Date] < CurrentDate &&
[Last Purchase Date] >= EOMONTH(CurrentDate, -1)
)
)
RETURN
LostCustomers

In this approach:

• The Last Purchase Date measure will calculate the last purchase date for each customer.
• The # Lost Customers 1m measure will then count the number of customers who made a purchase in the previous month but not in the current month.

Make sure to adjust the measures based on your data model and requirements. This approach should help you create a dynamic churn trend based on the criteria you've defined.

[Stefan_38]

Hi,

Thank you very much for your reply. I tried your approach and it has the same issue as my original measure as it won't work on a trend line as a customer cannot be considered lost if I made a purchase in the current period. A customer can be considered lost multiple month if you look at it month by month.  

[123abc]

Ah, I see what you're aiming for now. You want to track customers who were active in the previous month but are not active in the current month. This means you want to identify churned customers on a month-by-month basis.

To create a dynamic measure that captures customer churn on a monthly basis, you can use the following approach:

1. Identify Active Customers in the Current Month: This identifies customers who made a purchase in the current month.
2. Identify Active Customers in the Previous Month: This identifies customers who made a purchase in the previous month.
3. Calculate the Churned Customers: This identifies customers who were active in the previous month but are not active in the current month.

Here's how you can modify your DAX measures to capture this:

1. Measure to Identify Active Customers in the Current Month

Active Customers Current Month =
CALCULATETABLE(
VALUES(Financials[Account key]),
Financials[Line item] = "Units",
Financials[Date Key] <= MAX(Dim_Calendar[Date Key])
)

2. Measure to Identify Active Customers in the Previous Month

Active Customers Previous Month =
CALCULATETABLE(
VALUES(Financials[Account key]),
Financials[Line item] = "Units",
Financials[Date Key] <= EOMONTH(MAX(Dim_Calendar[Date Key]), -1) &&
Financials[Date Key] > EOMONTH(MAX(Dim_Calendar[Date Key]), -2)
)

3. Measure to Calculate the Churned Customers

Churned Customers =
VAR ActiveInPreviousMonth = COUNTROWS(Active Customers Previous Month)
VAR ActiveInCurrentMonth = COUNTROWS(Active Customers Current Month)
VAR Churned = ActiveInPreviousMonth - ActiveInCurrentMonth

RETURN
IF(Churned > 0, Churned, 0)

In this approach:

• Active Customers Current Month identifies customers who made a purchase in the current month.
• Active Customers Previous Month identifies customers who made a purchase in the previous month.
• Churned Customers calculates the churned customers by subtracting the number of active customers in the current month from the number of active customers in the previous month. If the result is positive, it returns the churned count; otherwise, it returns 0.

This method will allow you to capture the monthly churn of customers dynamically, considering only those who were active in the previous month but not in the current month.

[Stefan_38]

Thanks again for the help. I did try this approach but there seems to be an error in Measure 2, as it doesn't work in the row context and only with the total.  

[123abc]

You're correct. The context transition from row context to filter context can sometimes lead to unexpected results. Let's revise the approach to ensure that the measure works correctly in both row and filter contexts.

Instead of creating a separate measure for identifying customers who did not purchase in the current month, let's incorporate that logic directly into the measure that calculates the lost customers dynamically.

Here's how you can modify the approach:

1. Identify customers who purchased in the previous month:

Customers_LastMonth =
VAR LastMonthEndDate = EOMONTH(TODAY(), -2) -- Change TODAY() if needed
RETURN
CALCULATETABLE(
VALUES(Financials[Account key]),
Financials[Date Key] >= EOMONTH(LastMonthEndDate, -1) && Financials[Date Key] < LastMonthEndDate
)

2. Calculate the count of lost customers dynamically:

# Lost Customers 1m =
VAR LastMonthCustomers = Customers_LastMonth
VAR AllMonths = VALUES(Financials[Date Key])

VAR LostCustomersCount =
SUMX(
AllMonths,
VAR ThisMonthEndDate = [Date Key]
VAR ThisMonthCustomers =
CALCULATETABLE(
VALUES(Financials[Account key]),
Financials[Date Key] >= EOMONTH(ThisMonthEndDate, -1) && Financials[Date Key] < ThisMonthEndDate
)
VAR LostCustomers =
CALCULATE(
COUNTROWS(
EXCEPT(
LastMonthCustomers,
ThisMonthCustomers
)
)
)
RETURN
LostCustomers
)

RETURN
LostCustomersCount

In this revised approach, we keep the logic within the single measure # Lost Customers 1m. By using SUMX and iterating over each month, we calculate the count of lost customers dynamically.

This should help ensure that the measure works correctly in both row and filter contexts, providing you with a dynamic count of lost customers for each month.