DAX for Power BI - Easy Cohort Analysis

[@solidoffortitude]

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!

[@BIElite]

Glad to hear!!

[@Mrglasshalfempty]

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!

[@BIElite]

Hey there, thanks for feedback. I greatly appreciate the recommendation 😊

[@heliovictorfreire6825]

I was searching about Cohort Analysis for many months. Really insightful video. Thanks for sharing!!

[@BIElite]

That's great to hear Helio :)

[@kennethsmith9610]

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

[@BIElite]

That's a really interesting use case. Thanks for sharing!

[@walleyejim8903]

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.

[@laissalomao2058]

The best PBI Cohort video ever! Tkssss!!

[@irfanabbassi9202]

This is by far , one of the best videos on cohort... Can you please guide , if quarterly cohort is possible?

[@michalismichailides8935]

Finally a powerbi channel worth subscribing to. Good job

[@abhishekstatus_7]

Thanks for sharing this Parker !! It was really awesome! Always learning something special from you!

[@BIElite]

Thanks for watching abhishek!

[@bactran7799]

Thank you Elite, it is awesome. You make it in a very simple way that many people can touch.

[@tingyuliu3943]

The DAXs are smooooth. Thanks!

[@ranjanajoshi3099]

Thanks!

[@Filiperib]

How do I calculate row accumulated ?

[@aditilohia360]

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.

[@rexson75]

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 :-)

[@BIElite]

No problem Syed!

[@rexson75]

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.

[@michaelrall613]

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 ?

[@timto3935]

Hi, Thank you. Can you please do the running total from the above matrix?

[@princeshivansh18]

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.

[@carlosbittar413]

best cohort video EVER!

[@MitchellPearson]

As always, great job Parker.

[@BIElite]

Thanks Mitchell!

[@lalitjoshi3513]

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?

[@kuppireddydamodhar4493]

It is so good , please do video on churn, MRR, Retention rate,Atteration

[@BIElite]

Thanks! I'm doing a video today on employee retention (kind of like churn)

[@derbysutter2144]

Can this be done in direct query? Calculate didn’t get recognized

[@saimounica5580]

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

[@hiteshjoshi3148]

if we have the data model so in which table to add first order date

[@kevinsami5494]

This was awesome, but as a beginner, I struggled to adapt it to years vs months. Any help?

[@duniacollymore1151]

I have done this, but get stuck on the Customer retention, it retrives a blank value, any idea what happened?

[@Shiffo]

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.

[@aladdin_7780z]

Simple and elegant! Thank you.

[@huynhkhanhtamphan2285]

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?

[@ShivaKumar-oz6sx]

It was very use full..! how to do the lost customer like this...

[@whitewater4474]

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.

[@BIElite]

That's a really cool idea that would add a lot of depth to your report!

[@amakae1978]

Hi, how did you go about calculating the cumulative subscribes?

[@md.imrulhasan8757]

How to solve it for daily basis instead of month?

[@andrewsegawa7221]

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

[@jonathankieft9620]

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.

[@janwillemvanholst]

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]) )

[@tonildg9822]

this is what i also think. and also avoid future problems with circular dependencies.

[@fabianafsousa]

Why december is not there?

[@Ssssgls]

thanks for this video what Dax can I use for DAY wise retention analysis

[@rohitnair5958]

Thank you this was helpful Can you guide how to modify the dax if we need the data by week?

[@NadezhdaMalikova]

Hello, have you found a solution?

[@rohitnair5958]

@@NadezhdaMalikova nope

[@JuanJoséCastillo-e5o]

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.

[@georgek5657]

Did you manage to do this? and how?

[@sushmaladkat2439]

Hello, Could you please tell me, How i can convert this data per day and CW with year?

[@BiancaBi185]

very well explained!

[@crezeldacoetzee7985]

Super clear and easy to follow!! Thanks a stax!!

[@crezeldacoetzee7985]

Can you tell me how to get a cumulative %?

[@hindi-english1664]

will this calculation work? First Order Date = CALCULATE( MIN( Data[Order Date] ), FILTER( Data, Data[Customer Id]=EARLIER(Data[Customer Id]) ) )

[@yusufbas035]

I admire you. I wish I can be like you.

[@cristiancamilocorreabarrer30]

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 ?

[@jasonbeasley7092]

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!

[@allgeierschweiz]

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.

[@sunilsingh-ji8iz]

Thanks a lot for making this video.

[@jeffreyhill997]

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?

[@jeffreyhill997]

just giving this a friendly bump - thx Parker

[@sufi79109]

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

[@josephloures5517]

Amazing solution !

[@teerawutrattanamunee9186]

Thank you

[@EricaDyson]

Lovely! Thanks so much.

[@BIElite]

No problem, Erica!

[@ivanrybalchenko7225]

Thank you! Nice and clear!

[@nicolascegarra]

Hello, I can't modify the formulas to get the information totalized for years, currently you do it for months, can you help me?

[@mantune1]

Hi! I'd really appreciate if you could tell how did you do this, if you did it of course

[@SunilMathewMusic]

Fantastic 👍🏻

[@MrvladivostokMr]

not bad, but it is not working with SUM function...

[@heberabreu4595]

Nice video, THANKS!!

[@vuquangnam7782]

what should i do if i want cohort customer per week join?

[@NadezhdaMalikova]

Hello, have you found a solution?

[@hellmutmatheus2626]

dude you rock af

[@magicdimension6073]

So beauti-useful!

[@yagel81]

Amazing!

[@BIElite]

Thanks for watching!

[@marcuscarvalho335]

Thank you!

[@germanodamattaa.santana9879]

Thx bro

[@WillsuLX]

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?

[@allgeierschweiz]

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)