New Customer DAX

[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-yalanwu-msft]

Hi, @Antmkjr ,

I'm not sure what you want to show? According to my understanding, you can try the following measures.

New Customer or Not using Account = 
var _last=SUMMARIZE(FILTER(ALL('Sales'),YEAR([Purchase Date])<YEAR(MAX('Sales'[Purchase Date]))),[Accountno])
var _curr=SUMMARIZE(FILTER('Sales',YEAR([Purchase Date])=YEAR(MAX('Sales'[Purchase Date]))),[Accountno])
return COUNTROWS(DISTINCT(EXCEPT(_curr,_last)))

and filter on this visual.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Antmkjr]

this was what i was looking for as a calculated column

New customer =
VAR mycount =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[cust id] ),
            Table1[Date] < EARLIER ( Table1[Date] )
        )
    )
RETURN
    IF ( mycount = 0, "Yes" )

But this will consider my Customer as an Existing customer if he has only purchased in current year also. I want to mark him as Existing Customer if he has a purchase in previous year. How to filter the table for this.

[Nathaniel_C]

Hi @Antmkjr ,

Not sure if this is what you are looking to do.

Test = 
IF([New Customer or not Customer ID]= 1 || [New Customer or Not using Account]=1,"New Customer", "Existing Customer")

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[speedramps]

Thank you for providing PBIX example. I wish everyone did that !

Try this

Click here to download solution 

I have added a new page to your report with 3 visuals

The first gets which year the customer placed the first order

The second gets which year the new account placed the first order.

The third shows difference and if you click on difference then you view the details.

That is not excatly what you asked for, but what I think you need. Sorry if I have misunderstood. 

Incidentally, I recommend that you move the account number to the account dimension, rather than have it in the sales fact table.

If the account number is a group customer numbers then it should be a dim customer column.

If account number / customer numbers can change over time then use a SCD (slow channing dimension).

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 😎