Eliminate the overlapping data

[Anonymous]

Hello, I want to show the count of new clients and existing clients (by using cards) and filtered by month (using slicer), and here is an example of the issue.

Sample data:

Date	Client	Client Type (Calculated Column)
1/1/2020	A	New Client
2/1/2020	B	New Client
1/2/2020	A	Existing Client
4/2/2020	C	New Client
5/2/2020	C	Existing Client

The cards are showing: 

Number of new clients in February = 1

Number of existing clients in February = 2

The result I want:

Number of new clients in February = 1

Number of existing clients in February = 1 (because Client C is considered a new client in February)

Much appreciated if anyone could help.

[DataZoe]

@Anonymous You could try this calculated column:

Client Type =
IF (
    ISBLANK (
        CALCULATE (
            MIN ( 'Table'[Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Client] ),
            'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )
        || DATEDIFF (
            CALCULATE (
                MIN ( 'Table'[Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Client] ),
                'Table'[Date] < EARLIER ( 'Table'[Date] )
            ),
            'Table'[Date],
            MONTH
        ) = 0,
    "New Client",
    "Existing Client"
)

As as for the measures:

Existing Clients = CALCULATE(DISTINCTCOUNT('Table'[Client]),'Table'[Client Type]="Existing Client")

New Clients = CALCULATE(DISTINCTCOUNT('Table'[Client]),'Table'[Client Type]="New Client")-[Existing Clients]

Edit: Just noticed the requirement that if a client has 2 dates in the same month, they should still be considered a new client. I've adjusted the calculated column.

Edit 2: What you may be after is actually the gain/loss pattern. You can read about it here: https://www.daxpatterns.com/new-and-returning-customers/

[Anonymous]

Thank you, the link is really useful!! 🙂

[FarhanAhmed]

You can do it by

1- Add Client Type as Visual Filter to show only New or Existing Client

2- You can create a measure that @amitchandak  created.

[amitchandak]

@Anonymous , I am assuming the date format is dd/mm/yyyy

Try measures like

distinctcount(table[Client])
calculate(distinctcount(table[Client]),[Client Type] ="New Client")
calculate(distinctcount(table[Client]),[Client Type] ="Existing Client")

[Anonymous]

Hi @amitchandak , thanks for your reply but what I want to show is 

Number of new clients in February: 1 (Client C)

Number of existing clients in February: 1 (only Client A, no matter how many times client C appears in February is still considered as a new client) 

Much appreciated. 

[vivran22]

Hello @Anonymous 

You may try the following measures:

New Client = 
CALCULATE (
    DISTINCTCOUNT ( 'Client List'[Client] ),
    'Client List'[Client Type (Calculated Column)] = "New Client"
)


Existing Client = 
VAR _Summerize =
    SUMMARIZE (
        'Client List',
        'Client List'[Client],
        'Client List'[Client Type (Calculated Column)]
    )
VAR _Existing =
    SUMX (
        FILTER (
            _Summerize,
            'Client List'[Client Type (Calculated Column)] = "Existing Client"
        ),
        1
    )
VAR _New =
    SUMX (
        FILTER (
            _Summerize,
            'Client List'[Client Type (Calculated Column)] = "New Client"
        ),
        1
    )
VAR _Check =
   IF ( _New <  _Existing , _Existing - _New, _New- _Existing )
RETURN
    _Check

Output:

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

Hi @vivran22, thank you so much, this really solved the problem, but when the new client only appears once in a month, the _existing will still minus _new when it not supposed to minus it. 

For example: 

Date	Client	Client Type (Calculated Column)
1/1/2020	A	New Client
2/1/2020	B	New Client
1/2/2020	A	Existing Client
4/2/2020	C	New Client
5/2/2020	C	Existing Client
1/3/2020	D	New Client
2/3/2020	A	Existing Client
3/3/2020	C	Existing Client

With the measure, you provided it will show

Number of new clients in March = 1

Number of existing clients in March = 1 (it supposed to be 2 but with the _check equation, it will minus the number of new clients)

Is there any way that I could compare the [client with the condition of client type = new] and [client with the condition of client type = existing]?

Thanks,

[vivran22]

@Anonymous  is it safe to assume that the date of New Client will always be less than Existing client for the same client ID?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

@vivran22 yes, the date of new client is always less than the existing client

[vivran22 · Accepted solution]

@jmah

Try this:

 
Existing Clients =
var _existing = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "Existing Client")
var _new = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "New Client")
return COUNTROWS(EXCEPT(_existing, _new))
 
New Clients =
CALCULATE(
  DISTINCTCOUNT('Client List'[Client]),
  'Client List'[Client Type (Calculated Column)] = "New Client"
)

Bless you!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter