this is the only way I could understand from entire internet of how to calculate lost customers. This approach can be enriched by adding a parameter to select a window (2, 3 or 12 months as you wish) when your customers are considered as lost
Hi Sam, I was really impressed with your Dax calculation. Can u help me to get further understanding quiet easy than this , whether for Lost customer means the customers were not purchased the products for last 2 months ? And for the new customers are the customers who starts purchasing the products again while prior month failed to purchase it. Your comment is appreciated.
What you have described is how the logic has been set out in this tutorial. You can customize this further to whatever you like, it's understanding the technique which is the key here.
Thanks very much. I was able to create the measures and visuals using my dataset. When I create the visual on the right showing the Customer Name, Month & Year, and Lost Revenue, I want to add a fourth column showing the Customer's ID Number. However, adding this column to the visual is impacting the calculation and causing customers which were not actually lost to appear on the visual. Why does this the addition of this column impact the calculation when the Lost Customers and Customer Revenue Lost measures rely on tables containing only the Customer Name?
Thanks for sharing this video is really helpful. But I don’t know why it’s not working for me. After adding this measure I’m expecting to be able to have a table of month & year, and total number of lost customers as a second column. But P B gives me nothing. What am I missing?
Hello OZVID, We're not in a position to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference about what's working and not working in your report or data model. 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! If you haven't yet, you can subscribe to our TH-cam channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hello Sam! First of all, thank you very much for this tutorial. I've been struggling with this problem for too many days... I have one question, what did you do to show only the lost customers on that table on the right? When I click on a month on the bar chart, my table returns all customers, while yours shows only the lost ones.
Hello Cauê, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. We're so glad that you're finding our content helpful!!! Well, did you checked the Edit Interactions option? In our case, we're just selecting a particular month and the table on the right is getting filtered automatically and nothing else. 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. Also, make sure that you're subscribed to our TH-cam 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. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hi Sam. Great video. Also, I just finished an Udemy DAX course and it was very helpful. Question. Is there a way to limit the Customer revenue lost calculation to the current month? For example, calculate the lost revenue until today date. Thank you.
***** Related Links ***** 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/ New vs Existing Customers - Advanced Analytics w/DAX blog.enterprisedna.co/2018/01/24/new-vs-existing-customers-advanced-analytics-w-dax/ Counting Customers Over Time - DAX Example in Power BI blog.enterprisedna.co/2017/11/27/counting-customers-over-time-dax-example-in-power-bi/ ***** Related Course Modules ***** Advanced Analytics portal.enterprisedna.co/p/advanced-analytics Business Analytics portal.enterprisedna.co/p/business-analytics-series Unique Analytical Scenarios portal.enterprisedna.co/p/unique-analytical-scenarios ***** Related Support Forums ***** Lost Customers Measure forum.enterprisedna.co/t/lost-customers-measure/1039 Lost Customers Calculation forum.enterprisedna.co/t/lost-customers-calculation/1345 Lost/New Customers Each Year-Month forum.enterprisedna.co/t/lost-new-customers-each-year-month/2864 For more attrition analysis queries to review see here….. forum.enterprisedna.co/search?q=lost%20customers
Thank you for the video. Very useful as always! One question please, 'The time slicer on the left. Is a custom visual? I dont really like the look of the standard power bi slicer. Thank you!!
Hello Gabriel, Thank you for posting your query onto our channel and we really appreciate you taking time to post it. Well, no the time slicer showcased into the report towards the left side is not a "Custom Visual". It's actually a standard Power BI slicer, the reason why you might not be liking it is because the "Responsive" option is turned-off under the format settings of the slicer. Just follow the steps as provided below and you'll also the slicer in your report as has been showcased into our video. Below are the steps provided for the reference - 1). Click on the "Slicer" to activate the slicer visual. 2). Under the "Format" menu, click onto the "General" option. 3). Disable the "Responsive" option. Once you follow this steps, you'll observe that your slicer will also look like the slicer as has been showcased into the video. 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. Also, make sure that you're subscribed to our TH-cam 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. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
The formula is returning once the customer is lost, a value for every month after the customer is lost. I just want to mark and count the customer as lost when it actually is lost, not in the period after. My data is now showing like i am losing a lot of customers each month, but in reality we already lost them in the past and not every month again. Hoe should i change the formula?
Hello Nicholas Jeffries, thank you for posting your query onto our channel. Well, yes you can replace the 60 days and 365 days with the "DATEADD()" function in order to convert the calculation from "Days" to "Months". Below is the formula provided for reference - Calculation of Lost Customers - Lost Customers = VAR CustomersPurchased = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates ) , Dates[Date] > MIN( Dates[Date] ) - DATEADD( Dates[Date] , -1 , YEAR ) && Dates[Date] > MIN( Dates[Date] ) - DATEADD( Dates[Date] , -3 , MONTH ) ) VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates ) , Dates[Date] > MIN( Dates[Date] ) - DATEADD( Dates[Date] , -3 , MONTH ) && Dates[Date] > MIN( Dates[Date] ) ) ) RETURN COUNTROWS( EXCEPT( CustomersPurchased * PriorCustomers ) ) * -1 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'll find this formula useful and meets your requirements that you've been looking for. Happy Learning!!! forum.enterprisedna.co/
HI SAM, could you pls give a tip on how the filter works when you click the bar ? i put a table with customer name, but if i click the New customer calc, for example in 1 month there are 5 new, when i click the 5, the customer table does not automatically filter to show which are those 5 customers... is there a filtering trick here ?
Hello Stoneage8988, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well, you just need to ensure that the field which you're using from the table is linked with the other tables as well i..e, Fact Table linked with the Dimension Table. If you're using the fields from the Disconnected Table then it might not filter the results as expected. 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 TH-cam 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/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/ forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hello fani, Thank you for posting your query onto our channel and we really appreciate it. Well, the method used under the measures for "Attrition Value" and "Attrition Sales" is a "SWITCH( TRUE () )" statement. That is, under the measure attrition value, these are the measurs used - "New Customers" & "Lost Customers" and under the measure attrition sales, these are the measures used - "New Customer Sales" and "Customer Revenue Lost". Below are the measures for each of these metrics provided for the reference - New Customers = VAR CustomerTM = VALUES( Sales[Customer Name Index] ) VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates ) , Dates[Date] > MIN( Dates[Date] ) - 30 && Dates[Date] < MIN( Dates[Date] ) ) ) RETURN COUNTROWS( EXCEPT( CustomerTM , PriorCustomers ) ) Lost Customers = VAR CustomersPurchased = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates ) , Dates[Date] > MIN( Dates[Date] ) - 365 && Dates[Date] < MIN( Dates[Date] ) - 60 ) ) VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates ) , Dates[Date] > MIN( Dates[Date] ) - 60 && Dates[Date] < MIN( Dates[Date] ) ) ) RETURN COUNTROWS( EXCEPT( CustomersPurchased , PriorCustomers ) ) * -1 New Customer Sales = VAR CustomerTM = VALUES( Sales[Customer Name Index] ) VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates[Date] ) , Dates[Date] > MIN( Dates[Date] ) - 30 && Dates[Date] < MIN( Dates[Date] ) ) ) RETURN CALCULATE( [Total Sales] , EXCEPT( CustomerTM , PriorCustomers ) ) Customer Revenue Lost = VAR CustomersPurchased = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates ) , Dates[Date] > MIN( Dates[Date] ) - 365 && Dates[Date] < MIN( Dates[Date] ) - 60 ) ) VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer Name Index] ) , FILTER( ALL( Dates ) , Dates[Date] > MIN( Dates[Date] ) - 60 && Dates[Date] < MIN( Dates[Date] ) ) ) RETURN CALCULATE( CALCULATE( [Total Sales] , DATESBETWEEN( Dates[Date] , MIN( Dates[Date] ) - 365 , MIN( Dates[Date] ) - 60 ) ) , EXCEPT( CustomersPurchased , PriorCustomers ) ) * -1 Apart from the above measures which are being provided, 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 TH-cam channel pertaining to this topic i.e. Attrition Analysis as well as SWITCH( TRUE() ) statement in Power BI. 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 TH-cam 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. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/ blog.enterprisedna.co/analyze-who-your-lost-customers-are-using-power-bi-dax/ blog.enterprisedna.co/customer-attrition-analysis-advanced-dax-in-power-bi/ blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/ blog.enterprisedna.co/deep-dive-into-the-calculatetable-function-an-important-dax-formula-to-understand-well/ medium.com/@sam.mckay/analyze-customer-attrition-using-power-bi-f8207d50b98a blog.enterprisedna.co/using-advanced-dax-for-multiple-if-statement-in-power-bi/ blog.enterprisedna.co/using-switch-true-logic-in-power-bi-dax-concepts/ community.powerbi.com/t5/Community-Blog/SWITCH-True-Logic-In-Power-BI-What-s-Possible-With-This/ba-p/509753 th-cam.com/video/Ymy91W2K-EI/w-d-xo.html th-cam.com/video/h9kRwgamLcw/w-d-xo.html th-cam.com/video/GK-W25RM87Q/w-d-xo.html th-cam.com/video/l1VD89YqfO4/w-d-xo.html th-cam.com/video/UsAJPIANAb8/w-d-xo.html
Sam Im having a syntax issue with your code above. I notice at the end of Prior Customer there are three ) and at the and of CustomersPurchased there are two ). Is this correct?
Hello Sam, now here is my issue, I check for 120 days sale and 90 days no sale, to find who did not purchase for 3 months in a row, because we go by days value, 90, 120, it does not consider some months are 30 days some are 31 days, so the result is always varying as sale on 1st date of months gets excluded in some cases, can we convert day wise calculation to month wise, like for 365 use 12 and for 60 use 2.thanks In advance
Hello Abhishek, Thank You for posting your query onto our channel. Yes, this is definitely possible to convert the day wise analysis onto the month wise analysis. But 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. Also make sure that you're subscribed to our TH-cam 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/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
@@EnterpriseDNA Hi Sam, I did sign up for a membership and I was able to find the PBIX file and download it. Is it possible to enhance this so that it won't show the same "lost" customer month after month? In other words, only show the customer when he first gets to the lost status, but not after that.
Hi, I used the calculation of lost users. I wanted mine to be the ones that do not visit my website for he last 3 months. However, it just returns data of 3 months ago, instead of assuming that today we lost x users because they don't visit us for 3 months. How do I solve this?
Hello Beatriz Oliveira, Thank you for question. We appreciate your time. It is a little difficult to tell and provide the results without looking at the data structure, model and working of the PBIX file. So we encourage you to please write back to us to our Community Forum by providing a thorough description of the problem that you're having along with the PBIX file you are using as reference, as well as the mock-up results that you're trying to achieve. Our members and/or experts team will be able to assist you in a better and efficient manner. I have provided the link to our forum below: forum.enterprisedna.co/ For more queries, you can also reach out to us through our Community Forum where our members as well as Experts Team will be able to assist you, in a better and efficient manner. I hope this helps. Please make sure that you're subscribed to our TH-cam channel so you don't miss out any updates regarding Power BI. Along with it, you may also be interested to join our Power BI group on LinkedIn to receive latest updates. Links provided below for your reference. Happy Learning!!! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
It doesn't accept: DATESBETWEEN( invoice[Datum], MIN(invoice[Datum] ) - 365,MIN (invoice[Datum] ) - 60), ...it will not accept the 3rd MIN syntax what could that be?
Hello Panagiotis Diamantis, thank You for posting your query onto our channel. Well yes, with this they'll appear it as a lost customer since the logic behind this is "If a customer don't make any purchase in last 2 months then they're considered as a lost customer" and after 2 months when they make a purchase they're considered a new customer. If you want to have dynamic selection where you want to define your own period that if customer don't make any purchase with us within that period than they should be considered a lost than for this as well we have videos available onto our TH-cam channel. Below are some of the links provided for the reference in relation to the "Churn Analytics" topic. 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 provided of our Forum for the reference. Hoping you find this useful and meets your requirements that you've been looking for.🙂 Happy Learning!!! th-cam.com/video/h9kRwgamLcw/w-d-xo.html th-cam.com/video/Ymy91W2K-EI/w-d-xo.html th-cam.com/video/j_WNHWh3cUI/w-d-xo.html forum.enterprisedna.co/
Hello Sam! Great video. Is it possible to have a filter for different scenarios such as 6 months or 3 months? I understand that we need to modify the dax calculation instead of 60 to the selection value that I have on my filter. Thank you in advance!
![How To Use The GROUPBY Function In Power BI [2023 Update]](http://i.ytimg.com/vi/D3JLy0TbPrY/mqdefault.jpg)
![How To Use The GROUPBY Function In Power BI [2023 Update]](/img/tr.png)
this is the only way I could understand from entire internet of how to calculate lost customers.
This approach can be enriched by adding a parameter to select a window (2, 3 or 12 months as you wish) when your customers are considered as lost
Wow! Mind blowing stuff! Thank you, Sam!
Absolutely amazing !!! Thanks for sharing with us !!
Hi Sam, I was really impressed with your Dax calculation. Can u help me to get further understanding quiet easy than this , whether for Lost customer means the customers were not purchased the products for last 2 months ? And for the new customers are the customers who starts purchasing the products again while prior month failed to purchase it. Your comment is appreciated.
What you have described is how the logic has been set out in this tutorial. You can customize this further to whatever you like, it's understanding the technique which is the key here.
Thanks very much. I was able to create the measures and visuals using my dataset. When I create the visual on the right showing the Customer Name, Month & Year, and Lost Revenue, I want to add a fourth column showing the Customer's ID Number. However, adding this column to the visual is impacting the calculation and causing customers which were not actually lost to appear on the visual. Why does this the addition of this column impact the calculation when the Lost Customers and Customer Revenue Lost measures rely on tables containing only the Customer Name?
Thanks for sharing this video is really helpful. But I don’t know why it’s not working for me. After adding this measure I’m expecting to be able to have a table of month & year, and total number of lost customers as a second column. But P B gives me nothing. What am I missing?
Hello OZVID,
We're not in a position to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference about what's working and not working in your report or data model.
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! If you haven't yet, you can subscribe to our TH-cam channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
@@EnterpriseDNA thank you so much for sharing the link, will do 🙏🙏
The explanation of Except was back to front I believe?
if i want to add a distinct count function instead of values what changes should i do the formula??
Hello Sai, Did you manage to find a solution to this one? Cheers,
Hello Sam! First of all, thank you very much for this tutorial. I've been struggling with this problem for too many days... I have one question, what did you do to show only the lost customers on that table on the right? When I click on a month on the bar chart, my table returns all customers, while yours shows only the lost ones.
Hello Cauê,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. We're so glad that you're finding our content helpful!!!
Well, did you checked the Edit Interactions option? In our case, we're just selecting a particular month and the table on the right is getting filtered automatically and nothing else.
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. Also, make sure that you're subscribed to our TH-cam 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. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
sorry, just saw this, actually i have the same question, it does not correctly filter when clicking...
Hi Sam. Great video. Also, I just finished an Udemy DAX course and it was very helpful.
Question. Is there a way to limit the Customer revenue lost calculation to the current month? For example, calculate the lost revenue until today date. Thank you.
what does the syntax *-1 do? How does it add value to operate within the countrows statement?
Hi Mayra, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=Syntax%20Countrows
***** Related Links *****
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/
New vs Existing Customers - Advanced Analytics w/DAX
blog.enterprisedna.co/2018/01/24/new-vs-existing-customers-advanced-analytics-w-dax/
Counting Customers Over Time - DAX Example in Power BI
blog.enterprisedna.co/2017/11/27/counting-customers-over-time-dax-example-in-power-bi/
***** Related Course Modules *****
Advanced Analytics
portal.enterprisedna.co/p/advanced-analytics
Business Analytics
portal.enterprisedna.co/p/business-analytics-series
Unique Analytical Scenarios
portal.enterprisedna.co/p/unique-analytical-scenarios
***** Related Support Forums *****
Lost Customers Measure
forum.enterprisedna.co/t/lost-customers-measure/1039
Lost Customers Calculation
forum.enterprisedna.co/t/lost-customers-calculation/1345
Lost/New Customers Each Year-Month
forum.enterprisedna.co/t/lost-new-customers-each-year-month/2864
For more attrition analysis queries to review see here…..
forum.enterprisedna.co/search?q=lost%20customers
Thank you for the video. Very useful as always! One question please, 'The time slicer on the left. Is a custom visual? I dont really like the look of the standard power bi slicer. Thank you!!
Hello Gabriel,
Thank you for posting your query onto our channel and we really appreciate you taking time to post it.
Well, no the time slicer showcased into the report towards the left side is not a "Custom Visual". It's actually a standard Power BI slicer, the reason why you might not be liking it is because the "Responsive" option is turned-off under the format settings of the slicer.
Just follow the steps as provided below and you'll also the slicer in your report as has been showcased into our video. Below are the steps provided for the reference -
1). Click on the "Slicer" to activate the slicer visual.
2). Under the "Format" menu, click onto the "General" option.
3). Disable the "Responsive" option.
Once you follow this steps, you'll observe that your slicer will also look like the slicer as has been showcased into the video.
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. Also, make sure that you're subscribed to our TH-cam 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. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
@@EnterpriseDNA Thank you for taking the time to answer my question! It works great now!
Hi Sam, A very good movie. Liked it very much. This could be great if the PBIX and the source file made it generally available. Thanks.
The formula is returning once the customer is lost, a value for every month after the customer is lost. I just want to mark and count the customer as lost when it actually is lost, not in the period after. My data is now showing like i am losing a lot of customers each month, but in reality we already lost them in the past and not every month again. Hoe should i change the formula?
Hi Sam, this is incredible! Is there a way to use this measure that replaces the 60 days & 365 days (given that months vary in length)?
Hello Nicholas Jeffries,
thank you for posting your query onto our channel.
Well, yes you can replace the 60 days and 365 days with the "DATEADD()" function in order to convert the calculation from "Days" to "Months". Below is the formula provided for reference -
Calculation of Lost Customers -
Lost Customers =
VAR CustomersPurchased =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - DATEADD( Dates[Date] , -1 , YEAR ) &&
Dates[Date] > MIN( Dates[Date] ) - DATEADD( Dates[Date] , -3 , MONTH ) )
VAR PriorCustomers =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - DATEADD( Dates[Date] , -3 , MONTH ) &&
Dates[Date] > MIN( Dates[Date] ) ) )
RETURN
COUNTROWS(
EXCEPT(
CustomersPurchased * PriorCustomers ) ) * -1
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'll find this formula useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/
Thanks for sharing, how can we do the similar analysis YoY?
I would like to know this to. YTD YoY YTD
HI SAM, could you pls give a tip on how the filter works when you click the bar ? i put a table with customer name, but if i click the New customer calc, for example in 1 month there are 5 new, when i click the 5, the customer table does not automatically filter to show which are those 5 customers... is there a filtering trick here ?
Hello Stoneage8988,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well, you just need to ensure that the field which you're using from the table is linked with the other tables as well i..e, Fact Table linked with the Dimension Table. If you're using the fields from the Disconnected Table then it might not filter the results as expected.
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 TH-cam 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/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Hello, how to calculate measure attrition value and attrition sales ?
Hello fani,
Thank you for posting your query onto our channel and we really appreciate it.
Well, the method used under the measures for "Attrition Value" and "Attrition Sales" is a "SWITCH( TRUE () )" statement. That is, under the measure attrition value, these are the measurs used - "New Customers" & "Lost Customers" and under the measure attrition sales, these are the measures used - "New Customer Sales" and "Customer Revenue Lost". Below are the measures for each of these metrics provided for the reference -
New Customers =
VAR CustomerTM =
VALUES( Sales[Customer Name Index] )
VAR PriorCustomers =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - 30 &&
Dates[Date] < MIN( Dates[Date] ) ) )
RETURN
COUNTROWS(
EXCEPT( CustomerTM , PriorCustomers ) )
Lost Customers =
VAR CustomersPurchased =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - 365 &&
Dates[Date] < MIN( Dates[Date] ) - 60 ) )
VAR PriorCustomers =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - 60 &&
Dates[Date] < MIN( Dates[Date] ) ) )
RETURN
COUNTROWS(
EXCEPT( CustomersPurchased , PriorCustomers ) ) * -1
New Customer Sales =
VAR CustomerTM =
VALUES( Sales[Customer Name Index] )
VAR PriorCustomers =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates[Date] ) ,
Dates[Date] > MIN( Dates[Date] ) - 30 &&
Dates[Date] < MIN( Dates[Date] ) ) )
RETURN
CALCULATE( [Total Sales] ,
EXCEPT( CustomerTM , PriorCustomers ) )
Customer Revenue Lost =
VAR CustomersPurchased =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - 365 &&
Dates[Date] < MIN( Dates[Date] ) - 60 ) )
VAR PriorCustomers =
CALCULATETABLE( VALUES( Sales[Customer Name Index] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - 60 &&
Dates[Date] < MIN( Dates[Date] ) ) )
RETURN
CALCULATE(
CALCULATE( [Total Sales] ,
DATESBETWEEN(
Dates[Date] ,
MIN( Dates[Date] ) - 365 ,
MIN( Dates[Date] ) - 60 ) ) ,
EXCEPT( CustomersPurchased , PriorCustomers ) ) * -1
Apart from the above measures which are being provided, 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 TH-cam channel pertaining to this topic i.e. Attrition Analysis as well as SWITCH( TRUE() ) statement in Power BI.
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 TH-cam 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. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
blog.enterprisedna.co/analyze-who-your-lost-customers-are-using-power-bi-dax/
blog.enterprisedna.co/customer-attrition-analysis-advanced-dax-in-power-bi/
blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/
blog.enterprisedna.co/deep-dive-into-the-calculatetable-function-an-important-dax-formula-to-understand-well/
medium.com/@sam.mckay/analyze-customer-attrition-using-power-bi-f8207d50b98a
blog.enterprisedna.co/using-advanced-dax-for-multiple-if-statement-in-power-bi/
blog.enterprisedna.co/using-switch-true-logic-in-power-bi-dax-concepts/
community.powerbi.com/t5/Community-Blog/SWITCH-True-Logic-In-Power-BI-What-s-Possible-With-This/ba-p/509753
th-cam.com/video/Ymy91W2K-EI/w-d-xo.html
th-cam.com/video/h9kRwgamLcw/w-d-xo.html
th-cam.com/video/GK-W25RM87Q/w-d-xo.html
th-cam.com/video/l1VD89YqfO4/w-d-xo.html
th-cam.com/video/UsAJPIANAb8/w-d-xo.html
Sam Im having a syntax issue with your code above. I notice at the end of Prior Customer there are three ) and at the and of CustomersPurchased there are two ). Is this correct?
Hi Bryce, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=Churn%20Analytics
Amazing!! Thanks..
how to know our lost customers among all data not using a period of time?
Hello Sam, now here is my issue, I check for 120 days sale and 90 days no sale, to find who did not purchase for 3 months in a row, because we go by days value, 90, 120, it does not consider some months are 30 days some are 31 days, so the result is always varying as sale on 1st date of months gets excluded in some cases, can we convert day wise calculation to month wise, like for 365 use 12 and for 60 use 2.thanks In advance
Hello Abhishek,
Thank You for posting your query onto our channel.
Yes, this is definitely possible to convert the day wise analysis onto the month wise analysis. But 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. Also make sure that you're subscribed to our TH-cam 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/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Great video! Is the PBIX file and data source on your website?
Hi Scott, PBIX file is available in Enterprise DNA Online: portal.enterprisedna.co/
@@EnterpriseDNA Hi Sam, I did sign up for a membership and I was able to find the PBIX file and download it. Is it possible to enhance this so that it won't show the same "lost" customer month after month? In other words, only show the customer when he first gets to the lost status, but not after that.
@@scotth4514 I see someone has encountered same problem I have. I've searched around for the answer and haven't found an answer. Did you?
I didn't file the PBIX file can u help me to get the file thanks
Hi, I used the calculation of lost users. I wanted mine to be the ones that do not visit my website for he last 3 months. However, it just returns data of 3 months ago, instead of assuming that today we lost x users because they don't visit us for 3 months. How do I solve this?
Hello Beatriz Oliveira,
Thank you for question. We appreciate your time.
It is a little difficult to tell and provide the results without looking at the data structure, model and working of the PBIX file. So we encourage you to please write back to us to our Community Forum by providing a thorough description of the problem that you're having along with the PBIX file you are using as reference, as well as the mock-up results that you're trying to achieve. Our members and/or experts team will be able to assist you in a better and efficient manner.
I have provided the link to our forum below:
forum.enterprisedna.co/
For more queries, you can also reach out to us through our Community Forum where our members as well as Experts Team will be able to assist you, in a better and efficient manner.
I hope this helps.
Please make sure that you're subscribed to our TH-cam channel so you don't miss out any updates regarding Power BI. Along with it, you may also be interested to join our Power BI group on LinkedIn to receive latest updates. Links provided below for your reference.
Happy Learning!!!
Thanks and Warm Regards,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
It doesn't accept: DATESBETWEEN( invoice[Datum], MIN(invoice[Datum] ) - 365,MIN (invoice[Datum] ) - 60), ...it will not accept the 3rd MIN syntax what could that be?
Thanks!
Hello Sam. With this way a customer who was lost in March 2017 may appear as a lost again in April 2017. Is that right?
Hello Panagiotis Diamantis,
thank You for posting your query onto our channel.
Well yes, with this they'll appear it as a lost customer since the logic behind this is "If a customer don't make any purchase in last 2 months then they're considered as a lost customer" and after 2 months when they make a purchase they're considered a new customer.
If you want to have dynamic selection where you want to define your own period that if customer don't make any purchase with us within that period than they should be considered a lost than for this as well we have videos available onto our TH-cam channel. Below are some of the links provided for the reference in relation to the "Churn Analytics" topic.
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 provided of our Forum for the reference.
Hoping you find this useful and meets your requirements that you've been looking for.🙂
Happy Learning!!!
th-cam.com/video/h9kRwgamLcw/w-d-xo.html
th-cam.com/video/Ymy91W2K-EI/w-d-xo.html
th-cam.com/video/j_WNHWh3cUI/w-d-xo.html
forum.enterprisedna.co/
Hello Sam! Great video. Is it possible to have a filter for different scenarios such as 6 months or 3 months? I understand that we need to modify the dax calculation instead of 60 to the selection value that I have on my filter.
Thank you in advance!
Just use a WhatIf Parameter, then link the 60 days of the Formula to that parameter