I was skeptical in the beginning, like "Isn't this a typical aging report?", but I found a number of good ideas by watching it to the end that will help me in my own reports. Thanks as always!
Parker I've watched several Power BI "cohort" videos (this is a timely topic for me), and yours is by far the clearest and most elegant solution. Also cool you did the subscription revenue model, which is exactly what I need. Thanks so much man, and I recommended my best friend sign up for your class!
I'd like to see more Power BI videos on Higher Education related topics. I've created many reports, very few related to sales or financials. Mostly student related.
Thank you Elite, I wanted to understand If I add a filter of any category on this, then why I am not getting correct result at retention percentage calculations.
Thank you for sharing your analysis, step by step in detail. I was searching for this for a long time, at last. I found this very useful. Thanks Again :-)
Hi, @@BIElite I have one question about the data model, instead of date if we have a customer-id, product, week number, and purchase by week. How to approach this kind of scenario.
That is wonderful approach.. is it possible to show cohort analysis by weeks instead of months. I am not finding any supporting method to calculate the same cohort by weeks.
This is one of the simplest recordings I came across for cohort analysis. Thank you for this. How do we create a similar cohort for daily analysis instead of monthly? Could you help me with the relevant formula for capturing the Customer Retention
I really liked the video and can see it's practical use in a number of areas. Wondering if you think it's a good idea to add a summary line of persistent subscribers to cumulative subscribers YTD? I always learn a ton of cool things watching your videos. Thank you.
Please answer this im so confused, if in the next month the user comes again, will the user be counted again ? So each row is the total of a unique user from the first row ?
Hi Parker. Thanks for the video. One step I don't understand is why you needed to create the variable of CurrentCustomer. I know the column gives an incorrect result if you don't use the variable, I just don't know why.
Thanks. Very insightfull. Is using context transition in caculating the column First Order Date (EOM) more DAXesque? CALCULATE( EOMONTH(MIN(Data[Order Date]),0), ALLEXCEPT(Data, Data[Customer Id]) )
Wow this is great! I have been thinking about writing something to create a Cohort analysis. But this code was so simple that it's beautyfull. The only challenge i have, is how could i get this to work if a company has annual, quarterly and monthly invoicing. Meaning that the customer invoiced annually in January will still be a customer untill the next annual invoicing in next january next year. The idea i have is that i need to write functions first to split up these revenues to monthly revenues, to keep everything comparable.
Thanks for the enlightening video. I am failing to achieve this power-pivot. I am wondering how we can the "First Order Date (EOM)" in power pivot; coz am generating this analysis in an excel sheet. Please help
What if I also need to add a column between 0 and 1, which will give the data for the users who made a purchase in the same month as their joining month?
Sorry, but I have a project about customer retention. When I followed your instructions and inserted EOM, I realized that it doesn't cover enough months to match my transaction dates. Could you please help me with this?
Just wanted to say thanks for putting this video together. I have been searching for a good customer/vendor retention visual and just couldn't come up with anything useful. This was perfect. Quick question, is there a way to make this a drill through so that if I click any of the grid squares I can see what Customers or Vendor names are still being used. ie: If I started with 46 vendors, and 12 months later only 12 of the original 46 are being used, if I click the grid square with 12, is there a way to see which 12 vendors are in that list? Thanks in advance!
Better late than never? You could achieve that with a tooltip, but as scrolling is not an option in tool tips I suggest you look at using a drill through page instead. Put the measure on the drill through fields of the page and ensure Keep all filters is selected and you should be able to drill through to a customer list. The customer list should also have the Customer Retention measure on it so that it only shows the retained Customers and not all that signed up that month.
Thanks, very useful. Now, I have a question: Why not add another column with the diference Diff = DATEDIFF(Data[First Order Date (EOM)],Data[Order Date],MONTH) , add to columns table, and the count of users, show value as percent of row total. Is a bad practice ?
Hi, this formula has helped me a lot with cohort analysis. The thing is, my boss requested some analysis with cohorts from different years (2018-2022), and once I have dates from different years, the measures just stop working and I obtain a blank matrix as an outcome. Do you know why this could be happening? Thanks a lot in advance.
Hey Parker, this is great. For some reason though, when I try to view these retention % by year, the data completely vanishes as if it can't calculate a month 2 retention for all of 2022 (as an example). Any insight?
Parker, I want to ask one specific question. I have data with customer purchased more then one product and I want to perform cohort on product level. How can I do the cohort product wise with the same? Please help. Any leads would be really helpful
Thank you Parker! You got yourself a new subscriber! Can you also tell us the logic for churn customers? I can’t simply subtract this measure to the total customers because it turns into a mess. Do you have any content regarding this topic?
Better late than never? To calculate the Churn you need to figure out the difference in customers between Months. Here is a simple measure to calculate the churn in absolute numbers. The Churn rate then should be simple enough to work out... Churn = VAR CurrentFirstOrderDate = SELECTEDVALUE('Data'[First Order Date (EOM)]) VAR MonthsAfter = SELECTEDVALUE('Months After'[Value]) VAR PreviousMonthsAfter = SELECTEDVALUE('Months After'[Value])-1 RETURN if (PreviousMonthsAfter>=0, -- only do this calculation if we're past the initial Month CALCULATE( DISTINCTCOUNT('Data'[Customer ID]), FILTER( 'Data', EOMONTH('Data'[Order Date],0) = EOMONTH(CurrentFirstOrderDate,PreviousMonthsAfter) ) ) - CALCULATE( DISTINCTCOUNT('Data'[Customer ID]), FILTER( 'Data', EOMONTH('Data'[Order Date],0) = EOMONTH(CurrentFirstOrderDate,MonthsAfter) ) ) ,0)
![Seungmin "그렇게, 천천히, 우리(As we are)" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/kAzmhLHePqU/mqdefault.jpg)
I was skeptical in the beginning, like "Isn't this a typical aging report?", but I found a number of good ideas by watching it to the end that will help me in my own reports. Thanks as always!
Glad to hear!!
Parker I've watched several Power BI "cohort" videos (this is a timely topic for me), and yours is by far the clearest and most elegant solution. Also cool you did the subscription revenue model, which is exactly what I need. Thanks so much man, and I recommended my best friend sign up for your class!
Hey there, thanks for feedback. I greatly appreciate the recommendation 😊
I was searching about Cohort Analysis for many months. Really insightful video. Thanks for sharing!!
That's great to hear Helio :)
We use this in Higher Education to track Student Retention/Graduation by Entering Cohort from Term to Term and Year to Year. Very nice video
That's a really interesting use case. Thanks for sharing!
I'd like to see more Power BI videos on Higher Education related topics. I've created many reports, very few related to sales or financials. Mostly student related.
This is by far , one of the best videos on cohort...
Can you please guide , if quarterly cohort is possible?
The best PBI Cohort video ever! Tkssss!!
Thanks for sharing this Parker !! It was really awesome! Always learning something special from you!
Thanks for watching abhishek!
Finally a powerbi channel worth subscribing to. Good job
Thank you Elite, it is awesome. You make it in a very simple way that many people can touch.
Thank you Elite, I wanted to understand If I add a filter of any category on this, then why I am not getting correct result at retention percentage calculations.
Thanks!
Thank you for sharing your analysis, step by step in detail. I was searching for this for a long time, at last. I found this very useful. Thanks Again :-)
No problem Syed!
Hi, @@BIElite I have one question about the data model, instead of date if we have a customer-id, product, week number, and purchase by week. How to approach this kind of scenario.
The DAXs are smooooth. Thanks!
That is wonderful approach.. is it possible to show cohort analysis by weeks instead of months. I am not finding any supporting method to calculate the same cohort by weeks.
Hi, Thank you. Can you please do the running total from the above matrix?
This is one of the simplest recordings I came across for cohort analysis. Thank you for this. How do we create a similar cohort for daily analysis instead of monthly? Could you help me with the relevant formula for capturing the Customer Retention
if we have the data model so in which table to add first order date
I really liked the video and can see it's practical use in a number of areas. Wondering if you think it's a good idea to add a summary line of persistent subscribers to cumulative subscribers YTD? I always learn a ton of cool things watching your videos. Thank you.
That's a really cool idea that would add a lot of depth to your report!
Hi, how did you go about calculating the cumulative subscribes?
Please answer this im so confused, if in the next month the user comes again, will the user be counted again ? So each row is the total of a unique user from the first row ?
How do I calculate row accumulated ?
Hi Parker. Thanks for the video. One step I don't understand is why you needed to create the variable of CurrentCustomer. I know the column gives an incorrect result if you don't use the variable, I just don't know why.
Thanks. Very insightfull.
Is using context transition in caculating the column First Order Date (EOM) more DAXesque?
CALCULATE(
EOMONTH(MIN(Data[Order Date]),0),
ALLEXCEPT(Data, Data[Customer Id])
)
this is what i also think. and also avoid future problems with circular dependencies.
Wow this is great! I have been thinking about writing something to create a Cohort analysis.
But this code was so simple that it's beautyfull.
The only challenge i have, is how could i get this to work if a company has annual, quarterly and monthly invoicing.
Meaning that the customer invoiced annually in January will still be a customer untill the next annual invoicing in next january next year.
The idea i have is that i need to write functions first to split up these revenues to monthly revenues, to keep everything comparable.
Thanks for the enlightening video. I am failing to achieve this power-pivot. I am wondering how we can the "First Order Date (EOM)" in power pivot; coz am generating this analysis in an excel sheet. Please help
What if I also need to add a column between 0 and 1, which will give the data for the users who made a purchase in the same month as their joining month?
Sorry, but I have a project about customer retention. When I followed your instructions and inserted EOM, I realized that it doesn't cover enough months to match my transaction dates. Could you please help me with this?
Simple and elegant! Thank you.
thanks for this video what Dax can I use for DAY wise retention analysis
Just wanted to say thanks for putting this video together. I have been searching for a good customer/vendor retention visual and just couldn't come up with anything useful. This was perfect. Quick question, is there a way to make this a drill through so that if I click any of the grid squares I can see what Customers or Vendor names are still being used. ie: If I started with 46 vendors, and 12 months later only 12 of the original 46 are being used, if I click the grid square with 12, is there a way to see which 12 vendors are in that list? Thanks in advance!
Better late than never? You could achieve that with a tooltip, but as scrolling is not an option in tool tips I suggest you look at using a drill through page instead. Put the measure on the drill through fields of the page and ensure Keep all filters is selected and you should be able to drill through to a customer list. The customer list should also have the Customer Retention measure on it so that it only shows the retained Customers and not all that signed up that month.
This was awesome, but as a beginner, I struggled to adapt it to years vs months. Any help?
As always, great job Parker.
Thanks Mitchell!
Thank you this was helpful
Can you guide how to modify the dax if we need the data by week?
Hello, have you found a solution?
@@NadezhdaMalikova nope
I have done this, but get stuck on the Customer retention, it retrives a blank value, any idea what happened?
Can this be done in direct query? Calculate didn’t get recognized
It is so good , please do video on churn, MRR, Retention rate,Atteration
Thanks! I'm doing a video today on employee retention (kind of like churn)
It was very use full..! how to do the lost customer like this...
Thanks, very useful. Now, I have a question:
Why not add another column with the diference Diff = DATEDIFF(Data[First Order Date (EOM)],Data[Order Date],MONTH) , add to columns table, and the count of users, show value as percent of row total. Is a bad practice ?
Hi, this formula has helped me a lot with cohort analysis. The thing is, my boss requested some analysis with cohorts from different years (2018-2022), and once I have dates from different years, the measures just stop working and I obtain a blank matrix as an outcome. Do you know why this could be happening? Thanks a lot in advance.
Did you manage to do this? and how?
Super clear and easy to follow!! Thanks a stax!!
Can you tell me how to get a cumulative %?
best cohort video EVER!
How to solve it for daily basis instead of month?
Hey Parker, this is great. For some reason though, when I try to view these retention % by year, the data completely vanishes as if it can't calculate a month 2 retention for all of 2022 (as an example). Any insight?
just giving this a friendly bump - thx Parker
Parker, I want to ask one specific question. I have data with customer purchased more then one product and I want to perform cohort on product level. How can I do the cohort product wise with the same? Please help. Any leads would be really helpful
Hello, Could you please tell me, How i can convert this data per day and CW with year?
will this calculation work?
First Order Date = CALCULATE(
MIN(
Data[Order Date]
),
FILTER(
Data,
Data[Customer Id]=EARLIER(Data[Customer Id])
)
)
Why december is not there?
Thanks a lot for making this video.
I admire you.
I wish I can be like you.
very well explained!
Thank you Parker! You got yourself a new subscriber! Can you also tell us the logic for churn customers? I can’t simply subtract this measure to the total customers because it turns into a mess. Do you have any content regarding this topic?
Better late than never? To calculate the Churn you need to figure out the difference in customers between Months. Here is a simple measure to calculate the churn in absolute numbers. The Churn rate then should be simple enough to work out...
Churn =
VAR CurrentFirstOrderDate = SELECTEDVALUE('Data'[First Order Date (EOM)])
VAR MonthsAfter = SELECTEDVALUE('Months After'[Value])
VAR PreviousMonthsAfter = SELECTEDVALUE('Months After'[Value])-1
RETURN
if (PreviousMonthsAfter>=0, -- only do this calculation if we're past the initial Month
CALCULATE(
DISTINCTCOUNT('Data'[Customer ID]),
FILTER(
'Data',
EOMONTH('Data'[Order Date],0) = EOMONTH(CurrentFirstOrderDate,PreviousMonthsAfter)
)
)
-
CALCULATE(
DISTINCTCOUNT('Data'[Customer ID]),
FILTER(
'Data',
EOMONTH('Data'[Order Date],0) = EOMONTH(CurrentFirstOrderDate,MonthsAfter)
)
)
,0)
Thank you!
Nice and clear!
Hello, I can't modify the formulas to get the information totalized for years, currently you do it for months, can you help me?
Hi! I'd really appreciate if you could tell how did you do this, if you did it of course
Amazing solution !
Lovely! Thanks so much.
No problem, Erica!
what should i do if i want cohort customer per week join?
Hello, have you found a solution?
Fantastic 👍🏻
Nice video, THANKS!!
not bad, but it is not working with SUM function...
So beauti-useful!
dude you rock af
Amazing!
Thanks for watching!
Thank you
Thank you!
Thx bro