cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jackj
Helper I
Helper I

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?

1 ACCEPTED SOLUTION

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


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

6 REPLIES 6
richbenmintz
Solution Sage
Solution Sage

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 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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!

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.

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


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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)


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors