Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Customer Churn Analysis

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 ...
DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

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.