cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaudMeethal
Helper II
Helper II

Finding New/Existing Customers

 

 Hi,

 

I need to create a simple calculated column indicating whether the client account has occured for the first time i.e. "New" or it has occured before i.e. "Existing".

 

Only the first occurrence need to be considered as "New", the rest of the occurrences, irrespective of the gap in occurrences, should all be considered as "Existing".

 

I found some related solutions which were pretty confusing. Is there a simple way to accomplish it?

 

I have attached the sample file below: 

Sample File

 

Thanks!

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated column formula

 

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Account Code]=EARLIER(Data[Account Code])&&Data[Created Date]<EARLIER(Data[Created Date])))=0,"New","Existing")

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

 

That will happen if the date is the Account code appears more than once on the same date.  If you want the second instance on the same day to be marked Existing, then there should a column of Time as well.  Share the dataset where you also have a Time column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated column formula

 

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Account Code]=EARLIER(Data[Account Code])&&Data[Created Date]<EARLIER(Data[Created Date])))=0,"New","Existing")

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hey! I just noticed that there are multiple entries (mostly 2) of "New" for a single account code in many cases.

Anyway that it can be rectified? 

Thanks

Hi,

 

I do not know whom you are replying to.  Did my solution not work?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry, should've tagged. @Ashish_Mathur your solution did work partially, I later noticed that there are multiple entries (mostly 2) of "New" for a single account code in many cases.

Hi,

 

That will happen if the date is the Account code appears more than once on the same date.  If you want the second instance on the same day to be marked Existing, then there should a column of Time as well.  Share the dataset where you also have a Time column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@Ashish_Mathur That makes sense. I don't have the time details, only date. So I summarised the data monthly/yearly in different tables where there won't be multiple entries for the same point in time. Then i used your solution. Thanks!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

You should not create a column. 

 

Refer to this article. https://www.daxpatterns.com/new-and-returning-customers/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hey Matt,

Thanks.

 

I have gone through this article before, but couldn't make it work.

 

Also it is mandatory for me to create a column as per the requirement I'm given.

Anyway I could do that?

I don’t have my pc so I can’t look at the file. Assuming there is a 1 to many relationship between customers and sales, and assuming the sales table has one row per shopping event, and assuming that your definition of a new customer is someone’s that has only 1 record in the sales table (Ie a customer that purchased once in 2002 is still “new”) and assuming that every customer in the sales table has at least 1 one transaction, then something like this should work

 

=if(calculate(countrows(sales))=1,”New”,”Existing”)

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@MattAllington Creative, but i need to maitain the first entry as new and the subsequent as existing.

Anyways Thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.