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