How to find out new customers?

[Anonymous]

Dear Power BI Guru,

Could you kindly help with the below question please?

I have three set of data (in 3 tables)

1. 2021 total sales by company (i.e. column A = list of company names, column B= total sales)

2. 2020 total sales by company

3. 2019 total sales by company

The above tables have duplicate company names. So I created a 4th table which includes unique company names from all 3 years.

Some customers are existing customers. They made a purchase in one of 3 years, 2 of 3 years or all 3 years. However, some of them are completely new customers. They didn't make any purchase in 2019 AND 2020 but only in 2021.

Currently, I learnt to work out new customers when comparing 2 years' data (i.e. 2021 vs 2020). Below is what I created:

New customers =

Var currentCustomers = VALUES('2021 Total Sales by Company'[Company])

Var pastcustomers = VALUES('2020 Total Sales by Company'[Company])

Var newcustomers = EXCEPT(currentCustomers,pastcustomers)

RETURN COUNTROWS(newcustomers)

My question is:

How can I create a calculation to find these completely new customers only exist in 2021? (when 2021, 2020 and 2019 data tables are given)

Thank you in advance for taking the time to help me!

[Ashish_Mathur]

Hi,

It is quite easy to solve this.  Share some data (in a format that can be pasted in an MS Excel workbook).

[AlexisOlson · Accepted solution]

Generally, it's a good idea to union/append the sales from separate years into a single table (with an additional column that specifies the year).

If you do that, then you can write your measure like this:

New customers =
VAR currentYear = MAX ( Sales[Year] )
VAR currentCustomers = CALCULATETABLE ( VALUES ( Sales[Company] ), Sales[Year] = currentYear )
VAR pastcustomers = CALCULATETABLE ( VALUES ( Sales[Company] ), Sales[Year] < currentYear )
VAR newcustomers = EXCEPT ( currentCustomers, pastcustomers )
RETURN
    COUNTROWS ( newcustomers )

The way you have it set up, you need to union all the prior years for it to work:

New customers =
VAR currentCustomers = VALUES ( Sales2021[Company] )
VAR pastcustomers = UNION ( VALUES ( Sales2020[Company] ), VALUES ( Sales2019[Company] ) )
VAR newcustomers = EXCEPT ( currentCustomers, pastcustomers )
RETURN
    COUNTROWS ( newcustomers )

[Anonymous]

Hi Alexis,

If you don't mind, may I ask a follow up question regarding this topic please?

As you already know, my 2019, 2020 and 2021 Total Sales by Company data are in 3 different tables and each of the tables doesn't contain a 'year' column. 

To work out whether or not a company made a purchase in my online store. Below was my method:

 

2019 purchased = IF(COUNT('2019 Total Sales by Company'[Company])=BLANK(),"n","Y")

2020 purchased = If(COUNT('2020 Monthly Sales by Company'[ID])=BLANK(),"n","Y")

2021 purchased = IF(COUNT('2021 Total Sales by Company'[ID])=BLANK(),"n","Y")

Then I selected 'table' as my visualization in Power BI: 

Column A is a list of unique company names (I prepared a table listing all unique company names for this analysis). Column B-D are from above calculations. 

Then I applied filters to this visual to see which companies made purchases in all 3 years.

 My question is:

Is there a way I can do a calculation so that I can present the number of repeating companies in a 'Card' visual?

Thank you in advance!

Cathy

[AlexisOlson]

Maybe something like this?

All3Count =
COUNTROWS (
    FILTER (
        DISTINCT ( Company[ID] ),
        [2019 Purchased] & [2020 Purchased] & [2021 Purchased] = "YYY"
    )
)

[Anonymous]

Thank you Alexis for your swift reply!!! I just tried and it worked great!!! I was trying to figure it out this whole afternoon..You are a star!!! 🌟 Do you know how I can learn DAX in a more systematic way? Normally if I encounter a problem, I either try to write DAX by myself after learning from Youtube videos or ask a question in this community. I'd like to know a better way to learn it (i.e. some training courses you'd recommend). Thank you 🙂

[AlexisOlson]

Almost all of my learning is hands-on either at my job or answering questions from folks like you. The SQLBI guys have good courses but there's no substitute for many many hours of getting your hands dirty with real-life problems.

[Anonymous]

Hi Alexis,

Thank you for your help!! I appended the prior years into one column and used my DAX and it worked! I will try your method in my future analysis. 

Thank you!!

Cathy