Customer Churn Analysis

[Anonymous]

Hi All, 

I'm new to Power BI and got struck while performing Churn analysis on customer data. I need help with Churn Rate analysis for customer in the country has churned 125 days after ordering his last online order. 
Can someone help me in DAX Logic, I need this for my project on urgent basis.

For eg:
If we are checking in churn rate in Month of May, 2022
Count of distinct customer who placed an order: 1000

125 Day in past : Jan 2022

Count of distinct customer who placed an order: 2400

Churn Rate: (1000/2400)*100 = 41.67%

This needs to be dynamic
if the month changes it should calculate (End of that Month -125 Days) and use it to calculate Churn Rate.

Thanks in Advance!

[Whitewater100]

Hi:

You should have a Date Table, continuous and marked as DATE Table. Let's name it "Dates". This is connected to your Sales Fact Table, we'll call this table "Sales". In sales each customer has a unique ID eg, CustomerID

LOST CUSTOMER = 

var custlist = ALL(Sales[CustomerID])
var churndays = 125
Return
COUNTROWS(
                     FILTER(
                       custlist, 
                      CALCULATE(COUNTROWS(Sales), 
                   FILTER(ALLSELECTED(Dates), 
             Dates[Date] > MIN Dates[Date] - churndays) && 
             Dates[Date] < MIN(Dates[Date]))) = 0 ))

It's key to have your model designed for Fact and Dimension tables like Dates, Customers, Products, etc.

I hope this works. You can just change the churndays variable if you want to see another view. Even the churndays can be made dynamic if needed.

[daXtreme]

This topic has been beaten to death. Please refer to this: New and returning customers – DAX Patterns

[Anonymous · Accepted solution]

Hi @Anonymous ,

I'd like to suggest you to create a independent calendar table as slicer.

Please refer this formula to get the count of customer in a specific month selected in the slicer.

count_customer =
CALCULATE (
    DISTINCTCOUNT ( 'table'[customer] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        YEAR ( 'table'[date] ) = YEAR ( SELECTEDVALUE ( 'calendar'[date] ) )
            && MONTH ( 'table'[date] ) = MONTH ( SELECTEDVALUE ( 'calendar'[date] ) )
    )
)

 And this formula is to get the count of customer in the month of 125 Day ago.

count_customer_125 =
VAR _125 =
    SELECTEDVALUE ( 'calendar'[date] ) - 125
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'table'[customer] ),
        FILTER (
            ALLSELECTED ( 'table' ),
            YEAR ( 'table'[date] ) = YEAR ( _125 )
                && MONTH ( 'table'[date] ) = MONTH ( _125 )
        )
    )

Then the last measure to get the Churn Rate:

Churn Rate = count_customer/count_customer_125

Best Regards,

Jay

[amitchandak]

@Anonymous ,refer

https://blog.ineuron.ai/Customer-Churn-Analysis-5PyOaWdwDF

Or do you need
Customer Retention :

I have done something for months here, but the same can be used for days

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497