cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cohort Analysis To Calculate How Many Distinct Customers Retained Every Month

Hi Everyone, 

I am trying to do Cohort Analysis on Customers who are retained in subsequent months.Please see the below excel which needs to be created in Power BI

 

Customers (Count)      
         
YearMonthInitial1M2M3M4M5M6M
201571,648822734690640610596
201581,498776711656626610 
201591,515787657643620  
2015101,498721659638   
2015111,377717682    
2015121,398741     
201611,467      

 

If my distinct Customers in July 2015 are 1,648, only 822 are retained out of those 1,648  after 1M(1month) and so on and so forth.

Similarly for subsequent months distinct customers are calculated and further calulations of retention are done based on the Initial Data.

 

Can someone please help me with the DAX functions and the method to proceed further.

 

Thanks,

Kanchan

6 REPLIES 6
Super User IV
Super User IV

Re: cohort analysis

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Note, Notifications are not working right now, so if you update this with data, please send me a Private message.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: cohort analysis

@Anonymous ,


If my distinct Customers in July 2015 are 1,648, only 822 are retained out of those 1,648  after 1M(1month) and so on and so forth.

Similarly for subsequent months distinct customers are calculated and further calulations of retention are done based on the Initial Data.

 

Can someone please help me with the DAX functions and the method to proceed further.


Actually you have clarified your table structure very well. In addtion would you please tell me what's the expected value? Are you trying to calculate come percentage like increase rate or decrease rate? To be general, you may refer to quick measure about how to create measures using DAX function in power bi to achieve what you want.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Re: cohort analysis

@v-yuta-msft  The table shown in my post is what my output should be. I have distinct customernames for past 6 months(Months are selected dynamically based on current month).

 

I checked quick measures also but could not find anything that could be used directly for this scenario. This is a quite complicated scenario. 

  Please let me know if you know what functions could be used.

 

Thanks!

Kanchan

Anonymous
Not applicable

Re: cohort analysis

@Greg_Deckler  Thank you for sharing the link.

I will make sure I follow all these basic rules while posting any questions in future 🙂

suebayes Frequent Visitor
Frequent Visitor

Re: Cohort Analysis To Calculate How Many Distinct Customers Retained Every Month

Hi @Anonymous 

How did you progress with this, am looking at doing the same?

sue

Highlighted
gmalekar Frequent Visitor
Frequent Visitor

Re: Cohort Analysis To Calculate How Many Distinct Customers Retained Every Month

If you could share the sample data.
But this will be the steps you follow.

1. Calculate the OrderPeriod in yyyy-mm format for each transaction ,

YEAR(Query1[entryDate])&VALUE(FORMAT(MONTH(Query1[entryDate]),"00"))
2. Calculate the Cohort Group for that transaction 
FORMAT(CALCULATE(MIN(Query1[entryDate]),
ALLEXCEPT(Query1,Query1[custId])),"yyyy-mm").
 
Then use summarize DAX function to get total customers 
 
 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors