cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

2 ACCEPTED SOLUTIONS
Community Champion

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 )
)

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.

Solution Sage

``````// 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``````

7 REPLIES 7
Solution Sage

``````// 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``````

Frequent Visitor

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  😀

Community Champion

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 )
)

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.

Regular Visitor

Thank you very much for this solution. Works for me

Frequent Visitor

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

Solution Sage

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.

Frequent Visitor

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!

Announcements