DAX for Power BI - Easy Cohort Analysis

Рет қаралды 59,561

Күн бұрын

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

Пікірлер: 91

@solidoffortitude 3 жыл бұрын

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 3 жыл бұрын

Glad to hear!!

@Mrglasshalfempty 3 жыл бұрын

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 3 жыл бұрын

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

@heliovictorfreire6825 3 жыл бұрын

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

@BIElite 3 жыл бұрын

That's great to hear Helio :)

@tingyuliu3943 38 минут бұрын

The DAXs are smooooth. Thanks!

@irfanabbassi9202 2 жыл бұрын

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

@laissalomao2058 8 ай бұрын

The best PBI Cohort video ever! Tkssss!!

@kennethsmith9610 3 жыл бұрын

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 3 жыл бұрын

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

@walleyejim8903 3 жыл бұрын

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.

@michalismichailides8935 3 жыл бұрын

Finally a powerbi channel worth subscribing to. Good job

@abhishekstatus_7 3 жыл бұрын

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

@BIElite 3 жыл бұрын

Thanks for watching abhishek!

@bactran7799 3 жыл бұрын

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

@BiancaBi185 Ай бұрын

very well explained!

@rexson75 3 жыл бұрын

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 3 жыл бұрын

No problem Syed!

@rexson75 3 жыл бұрын

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.

@Filiperib 2 жыл бұрын

How do I calculate row accumulated ?

@carlosbittar413 Жыл бұрын

best cohort video EVER!

@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

@MitchellPearson 3 жыл бұрын

As always, great job Parker.

@BIElite 3 жыл бұрын

Thanks Mitchell!

@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.

@whitewater4474 3 жыл бұрын

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 3 жыл бұрын

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

@amakae1978 2 жыл бұрын

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

@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 ?

@kuppireddydamodhar4493 3 жыл бұрын

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

@BIElite 3 жыл бұрын

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

@Shiffo 2 жыл бұрын

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.

@nandornagy3624 Жыл бұрын

Simple and elegant! Thank you.

@aditilohia360 11 ай бұрын

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.

@yusufbas035 2 жыл бұрын

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

@timto3935 3 жыл бұрын

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

@jonathankieft9620 3 жыл бұрын

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.

@kevinsami5494 10 ай бұрын

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

@md.imrulhasan8757 Ай бұрын

How to solve it for daily basis instead of month?

@crezeldacoetzee7985 Жыл бұрын

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

@crezeldacoetzee7985 Жыл бұрын

Can you tell me how to get a cumulative %?

@duniacollymore1151 4 ай бұрын

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

@cristiancamilocorreabarrer30 3 жыл бұрын

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 ?

@sunilsingh-ji8iz Жыл бұрын

Thanks a lot for making this video.

@jasonbeasley7092 3 жыл бұрын

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 2 жыл бұрын

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.

@ShivaKumar-oz6sx 2 жыл бұрын

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

@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?

@janwillemvanholst 3 жыл бұрын

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 2 жыл бұрын

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

@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?

@derbysutter2144 Жыл бұрын

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

@EricaDyson 3 жыл бұрын

Lovely! Thanks so much.

@BIElite 3 жыл бұрын

No problem, Erica!

@andrewsegawa7221 2 жыл бұрын

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

@sunandagandham1 3 жыл бұрын

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

@rohitnair5958 3 жыл бұрын

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

@NadezhdaMalikova 3 жыл бұрын

Hello, have you found a solution?

@rohitnair5958 3 жыл бұрын

@@NadezhdaMalikova nope

@germanodamattaa.santana9879 Жыл бұрын

Thx bro

@ivanrybalchenko7225 2 жыл бұрын

Thank you! Nice and clear!

@user-ux3vd8tx6y Жыл бұрын

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 11 ай бұрын

Did you manage to do this? and how?

@fabianafsousa 2 жыл бұрын

Why december is not there?

@sufi79109 3 жыл бұрын

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

@teerawutrattanamunee9186 Жыл бұрын

Thank you

@josephloures5517 3 жыл бұрын

Amazing solution !

@heberabreu4595 Жыл бұрын

Nice video, THANKS!!

@MrvladivostokMr 2 жыл бұрын

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

@sushmaladkat2439 2 жыл бұрын

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

@hindi-english1664 2 жыл бұрын

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

@marcuscarvalho335 3 жыл бұрын

Thank you!

@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

@magicdimension6073 2 жыл бұрын

So beauti-useful!

@hellmutmatheus2626 2 жыл бұрын

dude you rock af

@yagel81 3 жыл бұрын

Amazing!

@BIElite 3 жыл бұрын

Thanks for watching!

@ranjanajoshi3099 2 жыл бұрын

Thanks!

@WillsuLX 3 жыл бұрын

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 2 жыл бұрын

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)