Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started


Customer Retention Part 3: Period Of Stay – Cohort Analysis

Using PowerBI, analyze the customer behavior while making a purchase



Period Of Stay – Cohort Analysis provide visibility on how many customers were retained after their first date of purchase.


What is Cohort analysis.

As per Wikipedia

Cohort analysis is a subset of behavioral analytics that takes the data from a given data set (e.g. an EMRS, an e-commerce platform, web application, or online game) and rather than looking at all users as one unit, it breaks them into related groups for analysis. These related groups, or cohorts, usually share common characteristics or experiences within a defined time-span. Cohort analysis allows a company to “see patterns clearly across the life-cycle of a customer (or user), rather than slicing across all customers blindly without accounting for the natural cycle that a customer undergoes.”


Cohort Analysis is studying the behavioral analysis of customers. In this blog, we will try to understand the customers and sales relationship by representing customers in groups or cohorts based on their first purchase ever in a store with their coming visits in a year.  

In this analysis both Axes are time. We have time on both row and column. We observe how the customers reduced over a period from time from the first date of purchase. This is retention over a period of time. Here we are creating a month bucket. Percent retain after n months.


Assume there are 100 new customers (consumers who made the first purchase in the store) in Jan 2020. Out of these 100, how many customers came back in the second month (Feb 2020). Then how many returned in the third month (March -2020) and so on for every month in 2020.



We have 4 tables – Sales, Customer, Geography, and Item

Screenshot 2020-09-10 18.17.56.pngScreenshot 2020-09-10 18.17.59.pngScreenshot 2020-09-10 18.18.01.pngScreenshot 2020-09-10 18.18.09.png


Below are the new columns, we created to form the Cohort Analysis Graph.

  • A new column - First Sales. You can also consider this as the first purchase made by the customer. The filtering is based on the Customer.
  • Another new column named Age. This column describes the aging period of the purchase. Here, we take the Month difference of Sales Date and First Sales (in months).


First Sales = minx(FILTER(Sales,[Customer Id] =EARLIER([Customer Id])),[Sales Date])
Customer Age = DATEDIFF([First Sales],[Sales Date],MONTH)+1



We created two Date tables 

  • Date Joined with Sales Date
  • The first Date joined with First Sales.

Screenshot 2020-09-24 19.36.25.png


Next created a series using generate series and joined it with the Age column.


Customer Age Bucket = ADDCOLUMNS(GENERATESERIES(1,max(Sales[Customer Age])+1) ,"Age in Month" , "Month " &[Value])



Screenshot 2020-09-24 19.36.39.png


And then we created a matrix with First Sales Month on row and month Age on the column. This matrix is based on distinct customers.


Customers = DISTINCTCOUNT(Sales[Customer Id])



Screenshot 2020-09-24 19.41.04.png


Then we Create Retain % based on the First Month


Retain % = CALCULATE(divide(DISTINCTCOUNT(Sales[Customer Id]),CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =1)) , 'Customer Age Bucket'[Age] >1)



Screenshot 2020-09-24 19.40.55.png



Let us know what you think about these measures. Share your thoughts on Customer Retention Part 3.


You can get all my posts at


The file can found at


For a more detailed understanding of Cohort Analysis in Customer Retention, you can check out the webinar. You can also follow my channel (YouTube) and LinkedIn (profile) to get information on the upcoming webinars.