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