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)​
richbenmintz's avatar
richbenmintz
Resident Rockstar
5 years ago

Hi jackj,

 

Using the Data you provided I came up with the following Measure that creates a table variable to store all of the prior customer ids, then returns the distinct count of customerid's that existed in the prior periods.

Prior Purchase Customer = 
    var prior_cust_ids = CALCULATETABLE(VALUES(Cohort[Customer ID]), FILTER(all('Cohort'), 'Cohort'[Customer ID] in FILTER(all(Cohort[Customer ID]), 
            'Cohort'[Week Ending]  < SELECTEDVALUE(Cohort[Week Ending]))))
    return 
    CALCULATE(DISTINCTCOUNT(Cohort[Customer ID]), 'Cohort'[Customer ID] in prior_cust_ids)

 

Super simple pbix also attached

 

I hope that helps you out,

 

Richard 

  • 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!

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      Hi jackj ,

       

      give this a try

      Prior Purchase Customer Last 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], DESC))
    return 
    CALCULATE(DISTINCTCOUNT(Cohort[Customer ID]), 'Cohort'[Customer ID] in prior_cust_ids)
      • jackj's avatar
        jackj
        Helper I
        5 years ago

        Perfect!  This gives me the numbers I need.  One final, final question - thank you again for all this help!  How can I show a list of the Customer Id's that meet these criteria - those who visited the following week and those who did not?  Really struggling to wrap my head around all of this and it is so much easier in Excel.  Thanks so much for all of your assistance here.

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