Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

New Customer DAX formula

https://drive.google.com/file/d/1ulGkZ0whCkSV0yAdHQ5Hr6P7KoxYROFb/view?usp=sharing

 

In the attached file, I am using the below formulas to check if it is a new customer for a particular year, based on Customer ID and Account No seperately

 

New Customer or not Customer ID =
VAR
    Customers = VALUES( Sales[Customer ID] )
RETURN
CALCULATE( DISTINCTCOUNT(sales [Customer ID]),
    FILTER(
        Customers,
        CALCULATE( COUNTROWS( Sales ),
            FILTER( ALL( Dates ), Dates[Date] < MIN( Dates[Date] ) ) ) = 0 ) )
 
 
New Customer or Not using Account =
VAR
    Accountno = VALUES( Sales[Accountno] )
RETURN
CALCULATE( DISTINCTCOUNT(Sales[Accountno]),
    FILTER(
        Accountno,
        CALCULATE( COUNTROWS( Sales ),
            FILTER( ALL( Dates ), Dates[Date] < MIN( Dates[Date] ) ) ) = 0 ) )

 

 

But I want to re calculate this logic to check if Either Customer ID is existing OR Account No is existing in previous year, then consider it as a new customer. ie. to combine the above formulas in a single formula.

So that cases like below are covered. in this example we need to check either Customer ID is existing in previous year or Account No is existing in previous year.

 

 

Link to pbix attached.

 

How to restructure the formula for this?

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    New Customers? = 
IF (
    YEAR ( [Purchase Date] )
        = YEAR (
            CALCULATE (
                MIN ( Sales[Purchase Date] ),
                FILTER ( Sales, [Customer ID] = EARLIER ( Sales[Customer ID] ) )
            )
        ),
    1,
    0
)
    New Customers? = 
IF (
    YEAR ( [Purchase Date] )
        = YEAR (
            CALCULATE (
                MIN ( Sales[Purchase Date] ),
                FILTER ( Sales, [Customer ID] = EARLIER ( Sales[Customer ID] ) )
            )
        ),
    1,
    0
)

     

    The next judgments about your old and new accounts can be based on these two columns.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous 

     

    1. Is each AccountD associated with a customer?

    2. How many different AccountID's can a customer have?

    3. Can 2 different customers share an account?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      1. Is each AccountD associated with a customer?  -- Yes

      2. How many different AccountID's can a customer have? -- Mostly 1, but not always, so we want to check either Cust ID is matching OR Account No is matching

      3. Can 2 different customers share an account? -- No

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    New Customers? = 
IF (
    YEAR ( [Purchase Date] )
        = YEAR (
            CALCULATE (
                MIN ( Sales[Purchase Date] ),
                FILTER ( Sales, [Customer ID] = EARLIER ( Sales[Customer ID] ) )
            )
        ),
    1,
    0
)
    New Customers? = 
IF (
    YEAR ( [Purchase Date] )
        = YEAR (
            CALCULATE (
                MIN ( Sales[Purchase Date] ),
                FILTER ( Sales, [Customer ID] = EARLIER ( Sales[Customer ID] ) )
            )
        ),
    1,
    0
)

     

    The next judgments about your old and new accounts can be based on these two columns.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.