No video
In-Depth Customer Churn, New Customers And Lost Customers Examples In Power BI Using DAX
Рет қаралды 42,635
Күн бұрынWatch the 2022 Update of this video here: • In-Depth Customer Chur...
Here we take a look at some customer churning / lost customers & new customers examples with DAX. This is not easy and you need to understand a lot before you get to this point. Using a combination of many DAX functions you can achieve amazing things with the DAX formula language. Here is one example that really showcases that.
****Video Details****
0:31 - Introduction
1:42 - Overview
2:44 - Find Lost Customers
5:40 - Find New Customers
7:08 - Find Returning Customers
9:02 - Conclusion
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
FREE - Power BI Resources - enterprisedna.c...
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Online - portal.enterpri...
Enterprise DNA Events - enterprisedna....
**** Related Links ****
Analyze Who Your Lost Customers Are Using Power BI & DAX
blog.enterpris...
Customer Trend Analysis in Power BI Using DAX
blog.enterpris...
Discover How Many Sales Can Be Attributed To New Customers - Advanced Power BI Insights
blog.enterpris...
**** Related Course Modules ****
Unique Analytical Scenarios
portal.enterpri...
Advanced DAX Combinations
portal.enterpri...
Scenario Method Workshops
portal.enterpri...
**** Related Support Forums ****
In-Depth Customer Churn, New Customers & Lost Customers Examples In Power BI
forum.enterpri...
New Customer analysis Customer Revenue Lost is not clear
forum.enterpri...
Customer Churn Analysis sales compare with sales of Non-Member customers
forum.enterpri...
For more customer analysis queries to review see here…..
forum.enterpri...
@EnterpriseDNA 4 жыл бұрын
***** Related Links ***** Analyze Who Your Lost Customers Are Using Power BI & DAX blog.enterprisedna.co/2019/07/30/analyze-who-your-lost-customers-are-using-power-bi-dax/ Customer Trend Analysis in Power BI Using DAX blog.enterprisedna.co/2018/03/12/customer-trend-analysis-in-power-bi-using-dax/ Discover How Many Sales Can Be Attributed To New Customers - Advanced Power BI Insights blog.enterprisedna.co/2018/11/06/discover-how-many-sales-can-be-attributed-to-new-customers-advanced-power-bi-insights/ ***** Related Course Modules ***** Unique Analytical Scenarios portal.enterprisedna.co/p/unique-analytical-scenarios Advanced DAX Combinations portal.enterprisedna.co/p/advanced-dax-combinations Scenario Method Workshops portal.enterprisedna.co/p/scenario-method-workshops ***** Related Support Forums ***** In-Depth Customer Churn, New Customers & Lost Customers Examples In Power BI forum.enterprisedna.co/t/in-depth-customer-churn-new-customers-lost-customers-examples-in-power-bi/558 New Customer analysis Customer Revenue Lost is not clear forum.enterprisedna.co/t/new-customer-analysis-customer-revenue-lost-is-not-clear/1043 Customer Churn Analysis sales compare with sales of Non-Member customers forum.enterprisedna.co/t/customer-churn-analysis-sales-compare-with-sales-of-non-member-customers/1146 For more customer analysis queries to review see here….. forum.enterprisedna.co/search?q=customer%20analysis
@sokgat7019 7 жыл бұрын
Great job Sam. Thank you, thank you, thank you veru much!!! Always a pleasure to watch your videos. Greetings from Greece!!!!!
@jehanjoshi1750 4 жыл бұрын
can you please explain working of the part dates < min(dates)
@LucianO-mr3cn 7 жыл бұрын
Hi Sam, Thank you for showcasing this great technique! I replicated the model, it worked great, however I found out that in my case, the Returning Customers measure actually returns, apart from ''truly'' returning customers, also the New Customers (returned by the new customers measure). I don't yet fullly understand how a certain date is considered by following formula Dates[Date] > (MIN(Dates[Date]) - ChurnDate) && Dates[Date] < MIN(Dates[Date]), I mean why a customer who has made a unique purchase on Aug 30 for example is both considered as new and returning in my case. Please tell me, if possible, where I'm mistaken. Thank you again for all your contributions.
@texmexdragon2160 2 жыл бұрын
I have the exact same question....can someone please help to answer?
@8g8819 7 жыл бұрын
Great thank you for this videos, they help a lot of people
@craigporter6521 7 жыл бұрын
Thanks Sam - very useful!
@Mikkeldamsam 7 жыл бұрын
Great job again!
@UPHARANAND 4 ай бұрын
Timing 4:02. Last line, above green. I still couldn't understand the logic. Dates(date)> min(dates(date)- churn date) Min(dates) become the first date of the calendar and if I minus 90 days then it will show error because date is not available in the calendar
@vida1719 7 жыл бұрын
Great video and insights and really advanced dax
@leanh2770 6 жыл бұрын
great job
@wilsonmartins2658 Жыл бұрын
Top, great job .
@EnterpriseDNA Жыл бұрын
Hi Wilson, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
@dinnemokdezib6020 3 жыл бұрын
Hi thank you for this video! its helping me with my school project. However I have one question. I copied your formula and now it shows which customers are new in comparison with the total. So for example 250 customers in January and 50 of them are new. But I want to create a new table which shows me the names of the new customers with the revenue. When I select the specific month it shows me the total 250 customers.
@DanielWeikert 7 жыл бұрын
This is really awesome. Could you elaborate more on this advanced functions where you nest calculate / filter expressions within other calculate/filter expressions. Still struggling with fully understand them.
@EnterpriseDNA 7 жыл бұрын
In time I will through many videos. These are all covered in depth in the paid training through Enterprise DNA also.
@DanielWeikert 7 жыл бұрын
the VALUES function returns us a list of unique entries in a table but it simultaneously always respects our current filter context? meaning if we exclude sth from this unique entrylist within a dax expression some of them will be excluded? I thought I could always use the values function to return all unique entries from a tablecolumn no matter the current filter context. And one other question. Could you elaborate on how you create complex formulas like that? Do you start from the most inner one and than "work your way up" ? Thanks a lot Sam
@samanthapikus2134 3 жыл бұрын
@@EnterpriseDNA I have paid. In which section is this piece of work?
@UPHARANAND 4 ай бұрын
Can I get this file
@ruchperformive4854 3 жыл бұрын
can we implement this logic to calculate revenue churn (lost revenue/ add on revenue by existing customer/ downgrade in revenue by existing customer/ revenue from new customer)
@EnterpriseDNA 3 жыл бұрын
Hello ruch performive, thank You for posting your query onto our channel. Well please provide a descriptive question of what you're trying to achieve. For furthermore queries, please reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better manner along with the descriptive question by providing the screenshots of what results that you're trying to achieve. We're providing a link below of our forum where you can post your question to achieve the results that you're looking for. Hoping this helps you.🙂 Happy Learning!!! forum.enterprisedna.co/
@luisaquino5556 3 жыл бұрын
Awesome, thanks Sam. How would I go about this if I am analyzing lost customers based on if they have not submitted an order in the last 3 years but did in the 2 years prior? using this for new and returning customers as well
@EnterpriseDNA 3 жыл бұрын
Hello Luis Aquino, thank You for posting your query onto our channel. We're providing a few links below of our videos available onto our EDNA KZfaq channel pertaining to the topic "Churn Analytics". Also for furthermore queries, you can reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link provided of the forum as well for the reference. Hoping you find this useful and meets your requirements that you been looking for.🙂 Happy Learning!!! community.powerbi.com/t5/Community-Blog/Some-Ideas-and-Techniques-for-Customer-Churn-Analysis-in-Power/ba-p/991121 kzfaq.info/get/bejne/mcx0rLGBtq-upHU.html kzfaq.info/get/bejne/qtubZKajzs24g30.html kzfaq.info/get/bejne/oMWHgauIzZnHhX0.html kzfaq.info/get/bejne/j9OpbJSIl7GRdX0.html kzfaq.info/get/bejne/fbFdipVmt7OcZ4U.html forum.enterprisedna.co/
@mariakosberg9244 6 жыл бұрын
Actually I was wondering if anyone here has worked out the attrition rate of lost customers per month and made comparisons against different years. This is great. Is it possible to see the retention rate and churn of customer gained/filtered specific marketing activities as well as date. If we I go by month alone and my churn and retention rate is different to when I look at churn and retention rate based on marketing activities. I'm wanting to determine the longevity of customers based on the type of activities used. Thank you :)
@EnterpriseDNA 6 жыл бұрын
On you first questions - this is possible and a great example. I may look into this for a future tutorial. For you second question, that really will depend on setting up your model correctly with the marketing events being able to filter your sales data correctly to compare like for like
@sreekumarsl5156 4 жыл бұрын
Should have included/started with the data, data model, relationships behind
@davidfrrichards 6 жыл бұрын
Not sure your formula for Lost Customers is quite right. It doesn't seem to take into account that a customer cannot be considered Lost if they haven't yet been a New customer. If a customer signed up on Jan 1, 2018, then they shouldn't be considered Lost on Jan 1, 2017. If your list of customers is complete and unchanging from day 1 this would be a sufficient formula, however that doesn't seem practical for most use cases. Otherwise a great video, thanks for the post.
@EnterpriseDNA 6 жыл бұрын
You make a great point there actually. Updates to come shortly on this. Have a number of other things to share around this technique and will look to refine the formulas, and simplify them
@waltertiggeloven 6 жыл бұрын
I agree. I think the Lost Customers should not be found in the selection of ALL( Sales[Customer ID] ), which is within `every single customer`. I would prefer to find the Lost Customer in the selection of VALUES ( Sales[Customer ID] ) , which is in the selection of `customers who have bought before`. Even though: a(nother) great explanation. IMHO more important is getting insight in more complex analysis and understanding the way Power BI works, than in fully perfect business definitions. Sam expects us to stay awake ourselves. Sam: many thanks for your always nice and clear explanations. I just love your work. Warm greats from The Netherlands.
@khoinguyen3335 6 жыл бұрын
From my thought, the formula should be like this: Lost Customers = VAR customerlist=CALCULATETABLE(VALUES(Sales[Customer ID]),FILTER(ALL('Date'[Date]),'Date'[Date]=MIN('Date'[Date])-churndate && 'Date'[Date]
@ingab9286 5 жыл бұрын
@@EnterpriseDNA Wondering if there has been a follow-up video on this... thank you!
@danielejiofor3493 3 жыл бұрын
Great video. Please Any link to download the pbx file for code snippets for the measure values
@EnterpriseDNA 3 жыл бұрын
Hi daniel ejiofor, thanks for posting your comment in the video. All pbix file/datasets are available for download in the Enterprise DNA Online, which is accessible via Membership. Find link below. Thanks! portal.enterprisedna.co/
@saanusanjay2801 3 жыл бұрын
Instead of Churndate = 90, if we want to compare just year on year, what can be used ?
@EnterpriseDNA 3 жыл бұрын
Hello Sanjay Mohan, thank You for posting your query onto our channel. Well this is absolutely doable. And we also have some of the videos available on our KZfaq channel as well as onto our education portal. Below are the links provided for the reference. For furthermore queries, you can also write to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of our forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! community.powerbi.com/t5/Community-Blog/Some-Ideas-and-Techniques-for-Customer-Churn-Analysis-in-Power/ba-p/991121 kzfaq.info/get/bejne/mcx0rLGBtq-upHU.html forum.enterprisedna.co/t/in-depth-customer-churn-new-customers-lost-customers-examples-in-power-bi/558 kzfaq.info/get/bejne/j9OpbJSIl7GRdX0.html portal.enterprisedna.co/courses/236946/lectures/3936501 portal.enterprisedna.co/courses/305944/lectures/5099354 portal.enterprisedna.co/courses/305944/lectures/5099367 forum.enterprisedna.co/
@anshupandey5769 Жыл бұрын
Hi Sam I tried to implement this lost customer but it throwing error "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." Can you Please help here
@EnterpriseDNA Жыл бұрын
Hi Anshu, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. In this case, we don't know where you've committed the error while writing a DAX measure. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful! You can subscribe to our KZfaq channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/
@AbhishekSharma-ij9vv 3 жыл бұрын
Hello, is it possible for you to provide a dax code for lost customers each month, i have a data for 3 years of sales, i need to know total customers, new customers and lost customers each month, window is 90 days for lost customers. thanks
@EnterpriseDNA 3 жыл бұрын
Hello Abhishek, Thank You for posting your query onto our channel. Firstly, your question is quite contradictory here because in the first line you've suggested lost customers each month and at the end you're suggesting window is 90 days for lost customers. But still we'll try our best to help you here in this case. We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA KZfaq channel pertaining to this topic. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our KZfaq channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA community.powerbi.com/t5/Community-Blog/Some-Ideas-and-Techniques-for-Customer-Churn-Analysis-in-Power/ba-p/991121 blog.enterprisedna.co/analyze-who-your-lost-customers-are-using-power-bi-dax/ kzfaq.info/get/bejne/fbFdipVmt7OcZ4U.html kzfaq.info/get/bejne/mcx0rLGBtq-upHU.html kzfaq.info/get/bejne/qtubZKajzs24g30.html kzfaq.info/get/bejne/oMWHgauIzZnHhX0.html kzfaq.info/get/bejne/j9OpbJSIl7GRdX0.html
@montassarbenkraiem3029 2 жыл бұрын
and now ? how you can calculate Churn rate using those info ?
@EnterpriseDNA Жыл бұрын
Hello Montassar BENKRAIEM, Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it. We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA KZfaq channel pertaining to this topic. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful! You can subscribe to our KZfaq channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers,
Enterprise DNA forum.enterprisedna.co/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ kzfaq.info/get/bejne/mcx0rLGBtq-upHU.html community.powerbi.com/t5/Community-Blog/Some-Ideas-and-Techniques-for-Customer-Churn-Analysis-in-Power/ba-p/991121 forum.enterprisedna.co/t/new-customers-lost-customers/20497/4 forum.enterprisedna.co/t/cant-figure-out-cohort-analysis/18615/3
@hamzarafique3857 6 жыл бұрын
Amazing video Sam! I have one question though; how should I adjust my churndate if my sales data is on monthly bases?
@EnterpriseDNA 6 жыл бұрын
There's a bit to that. Too difficult to explain in a comment unfortunately. I would just have a play around with the formula and see what happens when you make small adjustments here and there.
@taimoorjamal 4 жыл бұрын
@@EnterpriseDNA Need this as well please. Stuck.
@henrikmolin3683 3 жыл бұрын
Thanks! I wonder if its possible to modify the DAX formula for New customers so it filtrate for new customers with an actual sales amount, that is Sales >0? As now, my dataset has some errors (for other reasons) and contains a few customers with no sales, and I don't want them as "New customer".
@EnterpriseDNA 3 жыл бұрын
Hello Henrik Molin, thank you for posting your query onto our channel. Well since your data has the errors and we're not sure what sort of error they're and therefore without looking at the error and the file we're not in a position to judge or provide the results. For furthermore queries, please write back to us onto our Community Forum by providing the description of the problem that you're facing along with the working of the PBIX file as well as the results that you're expecting so that our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping this helps you. Happy Learning!!! forum.enterprisedna.co/
@pierrechapar3421 2 жыл бұрын
Hi, I have a question regarding Lost Customer. Does it work with slicers from the sales table? I replicated it in my model, but when I set slicers and select for example a customerTier the values go crazy. (shows 500k of lost customers when I have les that 600)
@EnterpriseDNA 2 жыл бұрын
Hello Pierre, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Subscribe to our KZfaq channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/
@Justbrowsingsoflo Жыл бұрын
I tried this measure for returning customers within 90 days of their first purchase and on the daily level it's correct but when grouped by month, the total is incorrect. The measure is exactly the same. Month of April, I expected a 4, yet it displayed a 0. I've yet to figure out why at the monthly level it is wrong. I've tried all except as well
@EnterpriseDNA Жыл бұрын
Hello Justbrowsingsoflo, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful! You can subscribe to our KZfaq channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/
@tanababa 6 жыл бұрын
Thank you for a very informative video. However, I struggle to understand why the expression: Dates[Dates] < MIN (Dates[Date]) means actually "earlier days". How can a date be earlier than the minimal date? Thank you Tamir
@EnterpriseDNA 6 жыл бұрын
I've recently improved on the technique for this. See here - kzfaq.info/get/bejne/oMWHgauIzZnHhX0.html
@MaikHenkel 7 жыл бұрын
great idea - quick question, I was trying to built the same formula with my datasat, unfortunately I don't have customer ID's, I can only work with customer names (text) - is there a easy solution to adopt your anaylis on lost, new customers?
@EnterpriseDNA 7 жыл бұрын
Should be no different, just use the customer name column to iterate through.
@MaikHenkel 7 жыл бұрын
I failed but I enrolled to your resources and will try to use your working model - thanks
@EnterpriseDNA 7 жыл бұрын
Ok great, good luck
@pouinichan 6 жыл бұрын
hi i need help this formula doesnt work in direct query what can i do
@EnterpriseDNA 6 жыл бұрын
It's unlikely to work as Direct Query only supports very simple DAX formula unfortunately
@monismaqsood1982 3 жыл бұрын
From where can i download this data source ?
@EnterpriseDNA 3 жыл бұрын
Hi Monis Maqsood, thanks for posting your comment in the video. All pbix file/datasets are available for download in the Enterprise DNA Online, which is accessible via Membership. Find link below. Thanks! portal.enterprisedna.co
@adrianiordache2406 7 жыл бұрын
Idea for you man. Why don't you do similar videos for Tableau? You could do them side-by-side, or one after the other or whatever. I think it'd expand your followers and paid subscribers too.
@EnterpriseDNA 7 жыл бұрын
Not at the moment. Very focused on Power BI. I think Power BI is just getting started and soon enough will dominant the data viz space.
BI With Gurpreet
Рет қаралды 40 М.
MITutorials
Рет қаралды 12 М.