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

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!! ðŸ™‚