cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnuTomy
Helper V
Helper V

New Customer DAX

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.

 

AnuTomy_0-1656755722259.png

 

Link to pbix attached.

 

How to restructure the formula for this?

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @AnuTomy ,

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.

vyalanwumsft_0-1657183547655.png


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.

AnuTomy
Helper V
Helper V

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
Super User
Super User

Hi @AnuTomy ,

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")

Nathaniel_C_0-1656872170070.png


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




speedramps
Super User
Super User

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 ! 😎

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors