DAX for Power BI - Easy Cohort Analysis
HTML-код
- Опубликовано: 14 окт 2024
- Perform customer cohort analysis in Power BI! Cohort analysis allows you to see if your customers are continuing to order as time passes. You'll be able to segment your customers based on the first month or period that they ordered. This easy technique uses simple DAX functions and patterns that you'll definitely want to add to your toolbox.
To enroll in my introductory or advanced Power BI courses:
training.bieli...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Link to PBIX:
www.dropbox.co...
Connect with me on Twitter!
/ powerbielite
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 :)
This is by far , one of the best videos on cohort...
Can you please guide , if quarterly cohort is possible?
Thanks for sharing this Parker !! It was really awesome! Always learning something special from you!
Thanks for watching abhishek!
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.
The best PBI Cohort video ever! Tkssss!!
Thank you Elite, it is awesome. You make it in a very simple way that many people can touch.
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?
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
if we have the data model so in which table to add first order date
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.
Finally a powerbi channel worth subscribing to. Good job
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.
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.
The DAXs are smooooth. Thanks!
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.
Hi, Thank you. Can you please do the running total from the above matrix?
Simple and elegant! Thank you.
This was awesome, but as a beginner, I struggled to adapt it to years vs months. Any help?
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 ?
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.
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.
best cohort video EVER!
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)
As always, great job Parker.
Thanks Mitchell!
How do I calculate row accumulated ?
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 ?
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
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?
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
very well explained!
I admire you.
I wish I can be like you.
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?
thanks for this video what Dax can I use for DAY wise retention analysis
It was very use full..! how to do the lost customer like this...
Super clear and easy to follow!! Thanks a stax!!
Can you tell me how to get a cumulative %?
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?
Can this be done in direct query? Calculate didn’t get recognized
Thanks a lot for making this video.
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
I have done this, but get stuck on the Customer retention, it retrives a blank value, any idea what happened?
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
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)
Hello, Could you please tell me, How i can convert this data per day and CW with year?
How to solve it for daily basis instead of month?
will this calculation work?
First Order Date = CALCULATE(
MIN(
Data[Order Date]
),
FILTER(
Data,
Data[Customer Id]=EARLIER(Data[Customer Id])
)
)
Thank you!
Nice and clear!
Thank you
Lovely! Thanks so much.
No problem, Erica!
Nice video, THANKS!!
Amazing solution !
Why december is not there?
Fantastic 👍🏻
So beauti-useful!
dude you rock af
Thank you!
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!
Thanks for watching!
Thanks!
what should i do if i want cohort customer per week join?
Hello, have you found a solution?
Thx bro
not bad, but it is not working with SUM function...