Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Eliminate the overlapping data

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 Clie...
  • vivran22's avatar
    vivran22
    6 years ago

    @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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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: 

DateClientClient Type (Calculated Column)
1/1/2020ANew Client
2/1/2020BNew Client
1/2/2020AExisting Client
4/2/2020CNew Client
5/2/2020CExisting Client
1/3/2020DNew Client
2/3/2020AExisting Client
3/3/2020CExisting 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,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • vivran22's avatar
      vivran22
      Community Champion
      6 years ago

      @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