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 DateWeek EndingCustomer IDService
1/1/20211/3/2021123Daycare
1/8/20211/10/2021123Boarding
1/14/20211/17/2021123Daycare
1/1/20211/3/2021124Daycare
1/15/20211/17/2021124Daycare
1/1/20211/3/2021125Boarding
1/1/20211/3/2021126Boarding
1/8/20211/10/2021126Daycare
1/13/20211/17/2021126Daycare
1/2/20211/3/2021127Boarding
1/5/20211/10/2021127Daycare
1/1/20211/3/2021128Boarding
1/6/20211/10/2021128Boarding
1/2/20211/3/2021129Daycare
1/8/20211/10/2021130Daycare
1/15/20211/17/2021130Daycare
1/1/20211/3/2021131Daycare
1/9/20311/10/2021131Daycare
1/15/20211/17/2021131Daycare

 

Basically, I am looking to do a cohort analysis by week, showing the total # of users who visited during the week ending 1/3/21, and how many of those users returned the next week, the following week, etc.

 

There are lots of helpful examples for doing this by month, but I want to create this by week.  Any suggestions?

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

6 Replies

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