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

Cohort Analysis tutorial

Dear, 

I am new to Power BI. 

Could you please to provide me the tutorial of how to build Cohort Analsysis in Power BI?

I used to make CA in Tableau, and now my job requires me to build CA in Power BI. 

I searched on google how to build CA but not many sources taught CA in Power BI, there is 1 source but I have to pay fees. 😞 

 

I hope you could help me

Thanks so much!! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sreenathv Member
Member

Re: Cohort Analysis tutorial

You can download my pbix from the following link

 

https://we.tl/t-hQF2IvvDGd

 

 

View solution in original post

10 REPLIES 10
Community Support Team
Community Support Team

Re: Cohort Analysis tutorial

Hi ThoaNguyen,

 

According to your description, it seems that you want to get Cohort Analysis tutorial, right?

If possible, could you please inform me more detailed information(such  as your data sample  and your expecting output)? Then I will help you more correctly.

In addition, there are some posts for Cohort Analysis, you could try to refer to  Cohort Analysis - DAX  and  Customer cohort analysis for details.

 

Best Regards,

Zoe Zhi

Anonymous
Not applicable

Re: Cohort Analysis tutorial

Dear, 

Thank you for your reply. 

I want my result look like this in the picture attached. 

I want to see the percentage of customer (%) and the number of customers who re-purchase after their first purchase. 

I use the sample - superstore dataset(xls). I can't find the attach button here so I send you a link the has the dataset. 

 

Please download it and help me to build Cohort Analysis in Power Bi. 

Thanks for spending time to help me! 

 

 

Cohort Analysis example.PNG

sreenathv Member
Member

Re: Cohort Analysis tutorial

Based on your post, I assume you want to find the following

 

1) How many new customer IDs were created each quarter?
2) How many of them purchased in the subsequent quarters?
3) Show the ratio counts as a percentage.

For the sake of better understanding, I will illustrate the workings step by step so that you will get started on this and you will be able to modify the codes on your own to suit your requirements.


Add the following Columns to your "Orders" table.

 

Step1: Add a calculated column for Customer's First Purchase Date

CustomerFirstPurchaseDate =
MINX (
    FILTER ( ALL ( Orders ), Orders[Customer ID] = EARLIER ( Orders[Customer ID] ) ),
    Orders[Order Date]
)

Step2: Add a calculated column for Order's Quarter No

OrderQtrNo =
VAR BaseYear =
    YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) )
VAR OrderYear =
    YEAR ( Orders[Order Date] )
VAR MF = ( OrderYear - BaseYear ) * 4
RETURN
    Orders[Order Date].[QuarterNo] + MF

Step3: Add a calculated column for Customer's Q00

 

CustomerQ0 =
VAR BaseYear =
    YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) )
VAR CFPYear =
    YEAR ( Orders[CustomerFirstPurchaseDate] )
VAR MF = ( CFPYear - BaseYear ) * 4
RETURN
    Orders[CustomerFirstPurchaseDate].[QuarterNo] + MF

Step4: Add a calculated column for Customer's 1st Quarter as text ( like 2014-Q3 etc...)

 

FirstQuarterOfCustomer =
Orders[CustomerFirstPurchaseDate].[Year] & "-Q" & Orders[CustomerFirstPurchaseDate].[QuarterNo]

Step 5: Add a calculated column for Customer's Returning Quarters

 

ReturningQuarterNo =
"Q"
    & FORMAT ( Orders[OrderQtrNo] - Orders[CustomerQ0], "00" )

Step 6: Add a measure for finding the count of active customers.

 

ActiveCustomerCount = DISTINCTCOUNT(Orders[Customer ID])

Step 7: Add a measure to find the total number of customers activated each quarter.

TotalCustomers =
CALCULATE (
    DISTINCTCOUNT ( Orders[Customer ID] ),
    Orders[ReturningQuarterNo] = "Q00"
)

Step 8: Add a measure to find the percentages

CA = DIVIDE([ActiveCustomerCount],[TotalCustomers],0)

Here is the result...

CA.jpg

Anonymous
Not applicable

Re: Cohort Analysis tutorial

Dear, 

Thank you for your solution 

I applied you formular in my company data

I got an error of QuarterNo for my Customer1stPurchase

I assumed that because of the date format. When I adjusted Date Format in C2C1stpurchase column, it changed to different result. 

CustomerQ0 Error.PNG

CustomerQ0.PNG

C2C formula.PNG

 

when I changed to Date format

date format.PNG

I just tried the formular on sample_superstore; it gives the same error. 

Did you tried it on data sample_superstore? If yes, Could you share me your file please? 
Hope you help me to fix it!!

Thanks 

sreenathv Member
Member

Re: Cohort Analysis tutorial

You can download my pbix from the following link

 

https://we.tl/t-hQF2IvvDGd

 

 

View solution in original post

Anonymous
Not applicable

Re: Cohort Analysis tutorial

Dear, 

Once again, thank you so much for spending time helping me!

I can solve it right now! 

Many thanks and really appreciated

Anonymous
Not applicable

Re: Cohort Analysis tutorial

Hi, 

One more thing. 

Could you please to explain me at this point
VAR MF = (OrderYear - BaseYear)*4

 

Hope to hear from you!!

Thanks

sreenathv Member
Member

Re: Cohort Analysis tutorial

Quarter No will always get only values between 1 to 4. But we want to ignore the years and have a running quarter no.

 

For example, if 2014 January is Q1, then we want 2016 January as Q9 and not as Q1 again.

So we calculate the difference between 2016 and 2014, i.e. 2016-2014 = 2

 

MF = (2016-2014) = 2 * 4 = 8

For 2016 January, the quarter no will be calculated by default as Q1. Instead, we add MF (value 😎 to Q1 and get the result as Q9.

 

Hope it clarifies.

 

 

Anonymous
Not applicable

Re: Cohort Analysis tutorial

Dear, 

Thank you for your clear explanation. 

I understand it. 

Could you please explain what is the different between Order's Quarter No and Customer's Q00?

Because I saw you make a calculation of like this: 

ReturningQuarterNo =
"Q"
    & FORMAT ( Orders[OrderQtrNo] - Orders[CustomerQ0], "00" ) 

 

I dont know what is the differeces and why we have to minus it. 

Hope you could clarify!!

Thanks 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 365 members 3,740 guests
Please welcome our newest community members: