Forum Discussion

jackj's avatar
jackj
Helper I
5 years ago
Solved

Help with Cohort Analysis - By Week

I'm wondering if anyone can help with this problem - I've been trying to solve it for days with no progress.  I have a data table similar to this:   Service Date Week Ending Customer ID Servi...
Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi jackj,

    See the measure below, should do the trick

    Purchase Customer Next week = 
    var prior_cust_ids = CALCULATETABLE(VALUES(Cohort[Customer ID]), TOPN(1, FILTER(all('Cohort'), 'Cohort'[Customer ID] in FILTER(all(Cohort[Customer ID]), 
            'Cohort'[Week Ending]  > SELECTEDVALUE(Cohort[Week Ending]))), Cohort[Week Ending], ASC))
    return 
    CALCULATE(DISTINCTCOUNT(Cohort[Customer ID]), 'Cohort'[Customer ID] in prior_cust_ids)​
jackj's avatar
jackj
Helper I
5 years ago

Thank you!  This definitely helps!  One question - this will show each week the number of customers who have previously purchased at any point in time.  How would I restrict this to just showing the distinct count of customer ID's who purchased *last week* only, instead of looking back to the beginning of time?

 

Thank you so much!

jackj's avatar
jackj
Helper I
5 years ago

Thank you!!  This works!  One final question - how could I modify this to show, for each week's cohort, how many of those users returned the following week?

 

I very much appreciate your help!  This is tremendous.

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    5 years ago

    Hi jackj,

    See the measure below, should do the trick

    Purchase Customer Next week = 
    var prior_cust_ids = CALCULATETABLE(VALUES(Cohort[Customer ID]), TOPN(1, FILTER(all('Cohort'), 'Cohort'[Customer ID] in FILTER(all(Cohort[Customer ID]), 
            'Cohort'[Week Ending]  > SELECTEDVALUE(Cohort[Week Ending]))), Cohort[Week Ending], ASC))
    return 
    CALCULATE(DISTINCTCOUNT(Cohort[Customer ID]), 'Cohort'[Customer ID] in prior_cust_ids)​