Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 oc...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.