New Customer DAX formula

[Antmkjr]

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 · Accepted solution]

Hi, @Antmkjr 

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.

[Antmkjr]

@amitchandak @tamerj1 anyone pls?

[daXtreme]

Hi @Antmkjr 

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?

[Antmkjr]

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