cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
awiefoong
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 IDMonth-YearSubscription Status
TZ029Jan-20Active
TZ029Feb-20Active
TZ029Mar-20Active
TZ029Apr-20Active
TZ029May-20Active
TZ029Jun-20Active
TZ029Jul-20Active
TZ029Aug-20Active
TZ029Sep-20Active
TZ029Oct-20Active
TZ029Nov-20Active
TZ029Dec-20Active
TZ030Jan-20Active
TZ030Feb-20Active
TZ030Mar-20Active
TZ030Apr-20Active
TZ030May-20Active
TZ031Mar-20Active
TZ031Apr-20Active
TZ031May-20Active
TZ031Jun-20Active
TZ031Jul-20Active
TZ031Aug-20Active
TZ032Jan-20Active
TZ032Feb-20Active
TZ032Mar-20Active
TZ032Apr-20Active
TZ032May-20Active
TZ032Jun-20Active
TZ032Jul-20Active
TZ032Aug-20Active
TZ032Sep-20Active
TZ032Oct-20Active
TZ032Nov-20Active
TZ032Dec-20Active
TZ033Jan-20Active
TZ033Feb-20Active
TZ033Mar-20Active
TZ033Apr-20Active
TZ033May-20Active
TZ034Jan-20Active
TZ034Feb-20Active
TZ034Mar-20Active
TZ034Apr-20Active
TZ034May-20Active
TZ034Jun-20Active
TZ034Jul-20Active
TZ034Aug-20Active
TZ034Sep-20Active
TZ034Oct-20Active
TZ034Nov-20Active
TZ034Dec-20Active

 

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Community Champion
Community Champion

Hi, @awiefoong 

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

 

Picture4.png

 

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.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

daxer-almighty
Solution Sage
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

 

View solution in original post

7 REPLIES 7
daxer-almighty
Solution Sage
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

 

View solution in original post

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
Community Champion
Community Champion

Hi, @awiefoong 

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

 

Picture4.png

 

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.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Thank you very much for this solution. Works for me

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

daxer-almighty
Solution Sage
Solution Sage

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors