How to Dynamically Compare the status of my customers across time period?

[awiefoong]

Hi all,

I need some help with a situation I'm currently having with the following dataset: 

The Dataset shows only Active Customers, their ID and the date (month-year) they are active with us. What I would like to do is to make use of the Date Slicer (which is also used for other visuals/tables/measures) to calculate the retention rate of my customers within the selected period.

For example the selected period is from Apr 2020 to Sep 2020, then I would like to know "How many of the customers who are with us in Apr 2020 are still with us in Sep 2020"? There are FIVE customers who are active in Apr 2020; they are: 

Customer ID

TZ029

TZ030

TZ031

TZ032

TZ033

TZ034

And among the Five customers above, only THREE remained active in Sep 2020

Customer ID

TZ029

TZ032

TZ034

The Retention Rate is therefore = 3/5 = 60%

If I change the date selection, then the retention rate should change accordingly. Your suggestions are most appreciated. 

Here is the Full dataset:

Customer ID	Month-Year	Subscription Status
TZ029	Jan-20	Active
TZ029	Feb-20	Active
TZ029	Mar-20	Active
TZ029	Apr-20	Active
TZ029	May-20	Active
TZ029	Jun-20	Active
TZ029	Jul-20	Active
TZ029	Aug-20	Active
TZ029	Sep-20	Active
TZ029	Oct-20	Active
TZ029	Nov-20	Active
TZ029	Dec-20	Active
TZ030	Jan-20	Active
TZ030	Feb-20	Active
TZ030	Mar-20	Active
TZ030	Apr-20	Active
TZ030	May-20	Active
TZ031	Mar-20	Active
TZ031	Apr-20	Active
TZ031	May-20	Active
TZ031	Jun-20	Active
TZ031	Jul-20	Active
TZ031	Aug-20	Active
TZ032	Jan-20	Active
TZ032	Feb-20	Active
TZ032	Mar-20	Active
TZ032	Apr-20	Active
TZ032	May-20	Active
TZ032	Jun-20	Active
TZ032	Jul-20	Active
TZ032	Aug-20	Active
TZ032	Sep-20	Active
TZ032	Oct-20	Active
TZ032	Nov-20	Active
TZ032	Dec-20	Active
TZ033	Jan-20	Active
TZ033	Feb-20	Active
TZ033	Mar-20	Active
TZ033	Apr-20	Active
TZ033	May-20	Active
TZ034	Jan-20	Active
TZ034	Feb-20	Active
TZ034	Mar-20	Active
TZ034	Apr-20	Active
TZ034	May-20	Active
TZ034	Jun-20	Active
TZ034	Jul-20	Active
TZ034	Aug-20	Active
TZ034	Sep-20	Active
TZ034	Oct-20	Active
TZ034	Nov-20	Active
TZ034	Dec-20	Active

[Jihwan_Kim · Accepted solution]

Hi, @awiefoong 

Please check the below picture and the sample pbix file's link down below.

Retention Rate =

VAR startingmonthyear =

MIN ( Dates[Start of Month] )

VAR endingmonthyear =

MAX ( Dates[Start of Month])

VAR startingmonthyearcustomers =

CALCULATETABLE (

VALUES ( Customers[Customer ID] ),

Dates[Start of Month]= startingmonthyear

)

VAR endingmonthyearcustomers =

CALCULATETABLE (

VALUES ( Customers[Customer ID] ),

Dates[Start of Month] = endingmonthyear

)

VAR remainingcustomers =

INTERSECT ( startingmonthyearcustomers, endingmonthyearcustomers )

RETURN

DIVIDE (

COUNTROWS ( remainingcustomers ),

COUNTROWS ( startingmonthyearcustomers )

)

https://www.dropbox.com/s/nofmw5hvjjh4uso/awiefoong.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[daxer-almighty · Accepted solution]

// Assumptions:
// 1.   'Date Slicer' is a proper date table
//      with a Date column that covers all days
//      across all years present in the system. Mark
//      the table as a date table in the model.
// 2.   There's an int hidden column of the format YYYYMM
//      that is the unique identifier of each month. Call it
//      YearMonthID.
// 3.   'Date Slicer'[Date] joins to FactTable[Date]
//      where FactTable[Date] is the first day of the
//      relevant month. Please remove Month-Year from
//      the fact table and replace with the first day
//      of the month. Then join the tables.
// 4.   Hide all the fields in 'Date Slicer' that should
//      not be used in the UI, for instance, Date.
// 5.   I'm assuming that an entry in the FactTable means
//      a subscription was active for the user in question
//      during the month indicated by the Date field.
// 6.   In this model Subscription Status is redundant.


[Retention Rate] =
var FirstMonthID = MIN( 'Date Slicer'[YearMonthID] )
var LastMonthID = MAX( 'Date Slicer'[YearMonthID] )
var CustomersInFirstMonth =
    CALCULATETABLE(
        DISTINCT( FactTable[CustomerID] ),
        'Date Slicer'[YearMonthID] = FirstMonthID,
        ALL( 'Date Slicer' )
    )
var CustomersInLastMonth =
    CALCULATETABLE(
        DISTINCT( FactTable[CustomerID] ),
        'Date Slicer'[YearMonthID] = LastMonthID,
        ALL( 'Date Slicer' )
    )
var Ratio =
    DIVIDE(
        // # of customers present in both periods
        COUNTROWS(
            INTERSECT(
                CustomersInFirstMonth,
                CustomersInLastMonth
            )
        ),
        // # of customers present in the first period
        COUNTROWS(
            CustomersInFirstMonth
        )
    )
return
    Ratio

[awiefoong]

Many thanks for your kind assistance. I tried your suggestion and it works well too. Now I have two solutions thanks to you and @Jihwan_Kim  😀

[Jihwan_Kim · Accepted solution]

Hi, @awiefoong 

Please check the below picture and the sample pbix file's link down below.

Retention Rate =

VAR startingmonthyear =

MIN ( Dates[Start of Month] )

VAR endingmonthyear =

MAX ( Dates[Start of Month])

VAR startingmonthyearcustomers =

CALCULATETABLE (

VALUES ( Customers[Customer ID] ),

Dates[Start of Month]= startingmonthyear

)

VAR endingmonthyearcustomers =

CALCULATETABLE (

VALUES ( Customers[Customer ID] ),

Dates[Start of Month] = endingmonthyear

)

VAR remainingcustomers =

INTERSECT ( startingmonthyearcustomers, endingmonthyearcustomers )

RETURN

DIVIDE (

COUNTROWS ( remainingcustomers ),

COUNTROWS ( startingmonthyearcustomers )

)

https://www.dropbox.com/s/nofmw5hvjjh4uso/awiefoong.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[PatrickPSHS]

Thank you very much for this solution. Works for me

[awiefoong]

Many thanks! This works perfectly for me. Thank you so much!

[daxer-almighty]

Hi @awiefoong 

Data should be representative of the problem. Where are entries in the full set that have a subscription status different than Active? Can't see them...

Should I understand that if a user has an entry in the full set, it means the user was active in the corresponding month? But if this is the case, then the column Subscription Status is redundant.

Please clarify.

[awiefoong]

Yes the status column is indeed redundant in this context. The actual dataset is an appended dataset from multiple monthly active subscribers data tables. Hope that clarifies. Thanks again!