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:

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

 

 

 

 

  • 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

10 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you, the link is really useful!! ğŸ™‚

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        vivran22
        Community Champion
        6 years ago

        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

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago

    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.