Obtain Unique values within distinct values

[kbruce]

Hi Folks,

I am trying to obtain the number of uninque values within a measure of distinct values.  The goal is to display a stacked column that shows the total number of clients, the distinct number of clients for that year, recurring clients and the number of new clients for each year that did not appear in previous years.  

I've been able to display 3 of the 4 items in a single graph, but I am unable to correctly calculate the NEW clients for each year that do not appear in previous years.  I toyed around with "IF" statements, counta/x and tables with min dates but cannot figure out how to display this subset of data. 

My idea has been on the focus on an "IF" statement that checks for previous instances if a unique client is being referenced in previous years. 

The data I am using is very simple.  Client Name and Date (with hierarchy).

Client name     Date

A-client            January 2000

A-client            January 2010

A-client            January 2021

B-Client            January 2005

B-Client            January 2015

B-Client            January 2021

C-Client            January 2021

D-Client            January 2000

D-Client            January 2005

D-Client            January 2015

D-Client            January 2021

The results should have counted each NEW client for each year by comparing the current date with previous dates.

2000     2005     2010   2015     2021

2           1            0         0           1

[kbruce]

I'm almost there.  I did not take into consideration multiple instances of a client in the same year, but  @v-yiruan-msft did.  I believe that I am all set with both of these replies.

[kbruce]

Upon further review/tinkering,  @v-yiruan-msft solution is not correct and does not solve the last part of what is needed.  

Client A is listed multiple times in the same year @FrankAT is closer, but the measure calculating the column adds a 1 next to each of the duplicates, below.  I need to remove the duplicates to obtain an accurate count.

A-client            January 2000

A-client            January 2000

A-client            January 2000

[v-yiruan-msft]

Hi @kbruce ,

As checked the table data in your first post, these three clients A-client are from different years(2000,2010 and 2021) separately. So the client A-client is not duplicate value in same year... Could you please provide some sample data with duplicated clients and your actual expected result with correct calculation logic and specific examples. Thank you.

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @kbruce ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column to get the Year

Year = YEAR('Table'[Date])

2. Create two measures as below to get the count of new clients

Measure = 
VAR _minyear =
    CALCULATE ( MIN ( 'Table'[Year] ), ALL ( 'Table'[Year] ) )
VAR _curyear =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _preclients =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Client name] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Year] < _curyear )
    )
VAR _curclients =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Client name] ),
        FILTER ( 'Table', 'Table'[Year] = _curyear )
    )
RETURN
    IF (
        _curyear = _minyear,
        DISTINCTCOUNT ( 'Table'[Client name] ),
        COUNTROWS ( EXCEPT ( _curclients, _preclients ) )
    )

Count of new clients = SUMX ( VALUES ( 'Table'[Year] ), [Measure] )

Best Regards

[FrankAT]

Hi @kbruce ,

first create two new calculated column, one with year number the other like this:

Use the following measure in a matrix:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)