Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
Hi, @awiefoong
Please check the below picture and the sample pbix file's link down below.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
// 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
// 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
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 😀
Hi, @awiefoong
Please check the below picture and the sample pbix file's link down below.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you very much for this solution. Works for me
Many thanks! This works perfectly for me. Thank you so much!
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
44 | |
17 | |
12 |