Forum Discussion

Andrew_na_lang's avatar
Andrew_na_lang
Helper V
3 years ago
Solved

RETENTION

Hi Fam,


I am working on a retention chart. The idea is to know on a daily basis how many customers are churned and retained. 
The logic is: 

Customers who ordered between day-60 to day-31 ago from today and still order between day-30 to day-1 (today) are considered retained, they are churned otherwise. 

Today is day 1
Yesterday is day 2
9 July 2023 is day 30 
8 July 2023 is day 31
9 June 2023 is day 60

In my orders table, I have the following columns: 

1. order_id (unique)
2. customer_id (unique) 
3. time_pickup_date

The thing is, I want to start day 1 based on the date filter I set.  

 

Then, I want to visualize it where x-axis is the date, y-axis is count of customers (distinct). Legend is churn and retained. 

Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Andrew_na_lang ,

    Please have a try.

    Retention =
VAR SelectedDateRange = SELECTEDVALUE('Date'[Date])
VAR Today = TODAY()
VAR DaysBetween = DATEDIFF(EARLIER('Orders'[time_pickup_date]), Today, DAY)
VAR RetainedCustomers =
    CALCULATE(
        DISTINCTCOUNT('Orders'[customer_id]),
        FILTER(
            'Orders',
            DaysBetween >= 31
                && DaysBetween <= 60
                && 'Orders'[time_pickup_date] >= SelectedDateRange - 30
                && 'Orders'[time_pickup_date] <= SelectedDateRange - 1
        )
    )
VAR ChurnedCustomers =
    CALCULATE(
        DISTINCTCOUNT('Orders'[customer_id]),
        FILTER(
            'Orders',
            DaysBetween >= 31
                && DaysBetween <= 60
                && ('Orders'[time_pickup_date] < SelectedDateRange - 30 || 'Orders'[time_pickup_date] > SelectedDateRange - 1)
        )
    )
RETURN
    SWITCH(
        TRUE(),
        RetainedCustomers = 0 && ChurnedCustomers = 0, BLANK(),
        RetainedCustomers = 0, "Churned",
        ChurnedCustomers = 0, "Retained",
        RetainedCustomers, "Retained",
        "Churned"
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Andrew_na_lang ,

    Please have a try.

    Retention =
VAR SelectedDateRange = SELECTEDVALUE('Date'[Date])
VAR Today = TODAY()
VAR DaysBetween = DATEDIFF(EARLIER('Orders'[time_pickup_date]), Today, DAY)
VAR RetainedCustomers =
    CALCULATE(
        DISTINCTCOUNT('Orders'[customer_id]),
        FILTER(
            'Orders',
            DaysBetween >= 31
                && DaysBetween <= 60
                && 'Orders'[time_pickup_date] >= SelectedDateRange - 30
                && 'Orders'[time_pickup_date] <= SelectedDateRange - 1
        )
    )
VAR ChurnedCustomers =
    CALCULATE(
        DISTINCTCOUNT('Orders'[customer_id]),
        FILTER(
            'Orders',
            DaysBetween >= 31
                && DaysBetween <= 60
                && ('Orders'[time_pickup_date] < SelectedDateRange - 30 || 'Orders'[time_pickup_date] > SelectedDateRange - 1)
        )
    )
RETURN
    SWITCH(
        TRUE(),
        RetainedCustomers = 0 && ChurnedCustomers = 0, BLANK(),
        RetainedCustomers = 0, "Churned",
        ChurnedCustomers = 0, "Retained",
        RetainedCustomers, "Retained",
        "Churned"
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.