How Many New Customers Do You Have Each Month - Advanced Power BI Analytics
Вставка
- Опубліковано 6 вер 2024
- FULL WORKSHOP (member only access) - portal.enterpri...
Within this break out tutorial I discuss and showcase how you can calculate the amount of new customers you have each month out of all the customers that buy off you.
There's plenty to think about when implementing this type of analysis. This is relatively advanced DAX for Power BI but really high quality and robust analysis of your customers.
Good luck!
***Video Details***
3:46 - adding new parameter for churn time
4:08 - extending your time window dynamically
5:04 - calculating new customers
8:19 - sample evaluation
10:16 - comparing customers this month to any particular month
**** 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****
Discover How Many Sales Can Be Attributed To New Customers - blog.enterpris...
New vs Existing Customers - Advanced Analytics In Power BI - blog.enterpris...
Analysing Customer Trend Using DAX In Power BI - blog.enterpris...
****Related Course Modules****
Mastering DAX Calculations - portal.enterpri...
Budgeting and Forecasting - portal.enterpri...
Advanced Analytics in Power BI - portal.enterpri...
****Related Forum Posts****
New Customer, Product - Forecast Vs Actual - forum.enterpri...
New Customer Analysis with Power BI - forum.enterpri...
New Customer Analysis Question - Scenario Method Workshop - forum.enterpri...
For more new customer analysis queries to review see here - forum.enterpri...
*****Related Links*****
Discover How Many Sales Can Be Attributed To New Customers - blog.enterprisedna.co/discove...
New vs Existing Customers - Advanced Analytics In Power BI - blog.enterprisedna.co/new-vs-...
Analysing Customer Trend Using DAX In Power BI - blog.enterprisedna.co/custome...
*****Related Course Modules*****
Mastering DAX Calculations - portal.enterprisedna.co/p/mast...
Budgeting and Forecasting - portal.enterprisedna.co/p/budg...
Advanced Analytics in Power BI - portal.enterprisedna.co/p/adva...
*****Related Forum Posts*****
New Customer, Product - Forecast Vs Actual - forum.enterprisedna.co/t/new-...
New Customer Analysis with Power BI - forum.enterprisedna.co/t/new-...
New Customer Analysis Question - Scenario Method Workshop - forum.enterprisedna.co/t/new-...
For more new customer analysis queries to review see here - forum.enterprisedna.co/search...
I keep revisiting your videos and finding new nuggets of information/value!! Thank you very much for sharing your genius with us!!
Glad to know.
Wow!!!! What an incredible video. Thank you Sam!!!!
Hi Juliane, 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!
Hello, I hope there is anyone alive still here :) Can you please help with below issue? I was using fully the same logic but getting a syntax error.
New Customer =
VAR CustomerTM = VALUES(Sprzedaz[ID klienta])
VAR PriorCustomers = CALCULATETABLE(VALUES(Sprzedaz[ID klienta]),
FILTER(ALL(Dates),
Dates[Date] > MIN(Dates[Date] - [Churn Time Frame Value]&&
Dates[Date] < MIN(Dates[Date])))
Return
COUNTROWS(
EXCEPT(CustomerTM,PriorCustomers))
"The syntax for 'Return' is incorrect
Hi @user-ny6ub1mq7y,
In your code, it was observed that the closing parentheses were missing at some of the places in your second variable i.e., in "PriorCustomers". Below is the rectified version of the code provide for your reference:
New Customer =
VAR _Customer_TM =
VALUES( Sprzedaz[ID klienta] )
VAR _Prior_Customers =
CALCULATETABLE( VALUES( Sprzedaz[ID klienta] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] > MIN( Dates[Date] ) - [Churn Time Frame Value] &&
Dates[Date] < MIN( Dates[Date] ) ) )
RETURN
COUNTROWS(
EXCEPT( _Customer_TM , _Prior_Customers ) )
Please check your code before evaluating the results.
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our UA-cam channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
excellent!! does anyone can explain to me how to create the parameter Churn Time Frame Value?
Absolutely awesome technique Sam !!! Thanks for sharing it with us and have a great weekend !
Hi,
I am confused on this date filter. What will be the values of Dates[Date] and MIN(Dates[Date]) for every iteration,
If we take dates from 1/1/2019 - 31/12/2019?
how was *Churn Time Frame Value* was created? although i can see a table was created, but was the DAX to calculate Churn Time Frame Value?
Same question here. My DAX was not recognizing the Churn time Frame Value
Really helpful in creating a measure and good presentation.
Thank you
Hi K.R., 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!
Thanks for the great explanation. I have managed to find the number of new buyer/customers per month, but how would I calculate the sum of the new buyers/customers over the past 2 months. with the ability to filter to a certain month, which would show the last 2 months. Is this possible?
Thanks
Hello Shyam,
Thank you for posting your query onto our channel and we really appreciate you taking your time to post it and it's a good query that we'll definitely take into due consideration. But as of now, we're not in a better position to provide you with any results. 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.
We're also providing a link from our forum where similar type of query was posted pertaining to the Churn Analysis in terms of months and how our expert provided a solution to that problem.
Hoping you find this useful and helpful. Also, make sure that you're subscribed to our UA-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/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
forum.enterprisedna.co/t/new-customers-lost-customers/20497/4
How can we make the Churn Measures? its not mentioned in the tutorial. ?
it is not mentioned on purpose as a way to make you pay for his training course. I have a hate and love relationship with these type of channels. Most of these power bi channels provide high level useless information/and or what is called in the marketing industry clickbait but some are great. I understand people need to make a living and i still think the motivation behind these videos is deceptive.
Great presentation. Is the use of the min(dates) to determine the range that occurs at the beginning of the period rather the end of the period (immediate versus due)? It is an excellent technique. Thank you.
Such a great video Sam - I'm going to use this to show Repeat Callers over X period in a Contact Centre Report
Nice one, like it
Hello, I'm trying to use the meassure, on my dataset, but the results is distinct member Ids per month, not new customers...
New customers=
VAR
CustomerTM=VALUES('Monthly data'[ Member ID])
VAR
PriorCustomers=CALCULATETABLE(VALUES('Monthly data'[ Member ID]),
FILTER(ALL('Monthly data');,
'Monthly data'[TXN Date]>MIN('Monthly data'[TXN Date])-[Days without purchase Value] &&
'Monthly data'[TXN Date]
This formula is great, thank you for sharing it. I have one question though, when I filter by for example brand name the row contents of the results no longer match with the total. I thought I could fix this with an ALL statement in the VAR CustomerTM but no luck. Do you have any recommendations?
I have the same issue at the moment. Did you find a fix for that?
Also had this issue when I tried to view by SalesPerson. Enterprise DNA team?
How to calculate "Churn time Frame Value" ?
It isn't a calc, it's a 'what if' parameter value
Hello there, i followed you steps on this video but i got a problem. In the table where you got the New customer sales and Total sales, both columns sum up to the same amount for me. Is this correct? I guess not
Hello George,
Thank you for posting your query onto our channel.
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're not in a position to judge or provide you with the results/comments.
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 UA-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hi,
I have tried to make the list of the clients with the amounts (new sales), as you show it in the graph. But I couldn't. When I make a matrix with the Customers ID and the "measure" of the new clients does not show me anything.
Can you help me understand what I may be doing wrong please. Thank you in advance!!!!
Hi Mauro,
Really difficult to help without looking at your model/PBIX file.
A quick suggestion would be to make sure that nothing else on the report would change the evaluation context of your calculation resulting in blank.
Be sure to check out the Enterprise DNA forum for any specific queries, you can also post PBIX files so users can provide solutions.
forum.enterprisedna.co/
Regards
Enterprise DNA
A great, albeit short, tutorial Sam. Thank you.
I have a question though. Do you actually need to use the "table method" as you show in the video. Can't you actually calculate the number of new customers using measures to calculate [distinct count of customers over 90 days before current month + present month days] - [distinct count of customers in past 90 days before current month]? (ie. without the virtual tables)?
Or, calculate the distinct count of customers where (filter) the customers whose sales in previous 90 days sales are blank or 0... Wouldn't either of those methods work?
(Edit: I think that the correct comparison in the PriorCustomers filter is Dates[Date] >= MIN( Dates[Date] - [Churn Time Frame Value] )
Yes there are other ways, I just like this way much better. Is cleaner and more usable in a variety of situations
could you explain why you using the min(dates) -churntimeframe. Shouldn't it be the max? Thanks
In the monthly context, MIN( dates ) is returning the first date of that particular month. So the time window is bringing up x numbers of days (90 in the example) and comparing that customer set versus the customer set in the current context.
If you would take MAX instead of MIN you would loose the count of new customers. You simply would add the customers within the current context to the past x-days selection.
Hi! Awesome formula, thanks for sharing!
I have question.
How I could add in that formula or change it that it would calculate on a yearly basis new customer but disregards future sales from that new customer during a specific year?
Basically as an example, A buys a product 2019. She is a new customer compared to 2018. But she will also buy 2020. I would like to consider when filtering to 2020 level that this A customer is then old customer.
Doing this in a visualization where x-axis is year.
Thanks!
I think you placed the number of days you want where he types "Churn time frame value"
Very useful. Thank you!
Great video, great insights, great visualisations
Great video! Instead of using the MIN function, would it be the same to use the DATESINPERIOD function combined with the Churn Time Frame Value to determine the amount of days (or months) that the time window of analysis would be?
You couldn't also use this technique. Would achieve same result is dates are aligned exactly.
Thanks. I tried to use this technique but I got a message "A table of multiple values was supplied where a single value was expected". I couldn't make it work. What do you mean that dates are aligned exactly? that they are correctly sorted?
Hi, thanks for your great sharing! I see you have a slicer with analysis year of 2017, would that affect the analysis when input 90 days or more and not able to calculate 2016 data when it is in month Feb/Jan, etc?
It would be fine in this case because the ALL( Dates ) within the formula would disregard any context coming from any date slicer.
Thanks a lot. It would be great if there is a video or explanation to walk through how the dates being filtered in PriorCustomers. As I found it difficult to understand when mixed with date slicer, date window, etc.
Hi sam, very nide, but
How can I check if this is right? In excel.
Pretty difficult to do in excel, I actually don't even know how you could. You can check it in Power BI, by looking to find who you new customers actually are....that's for another video in the future
Hi. Maybe you can extract table for all the client who bought in the perior date ( eg 90 days) after than extract another table to the current period and do countif to check which customers exist in one table but not in the other. Hope I was clear and it will help you. I also want to try it next week :-)
Great . You are Bi god