How to count NEW clients in year X (distinct)

[Joris_NL]

Hi guys,

I really can't find a fitting formula for this one.

I need to count the unique number of clients each year, but only the ones that are new that year (compared to last year).

EDIT: I also want to know how many clients (new and old) left for good in those year.

Example table:

ClientNr	StartDate	EndDate
client2	1-1-2017	31-12-2020
client1	1-1-2021	31-12-2021
client3	1-1-2022	1-1-2023
client1	1-1-2022	1-1-2023
client2	1-1-2022	1-1-2023

I have a variable input for a specific year to show results for, including its 2 previous years. So I have a measure for this:

MeasureYear = [2022]

Meaning I want to count the number of new clients for 2022, 2021 and 2020. The result should be:

2020: 0

2021: 1 (so client 1)

2022: 2 (clients 3 and 2, but not 1)

For clients leaving for good would result in

2020: 1

2021: 0

2022: 0

What I got working so far is a filter to calculate only the last three years:

DATESINPERIOD('Table'[StartDate];DATE('Table2'[MeasureYear];12;31);-3;YEAR))

But not the formula itself to calculate 😞

Please help lol!

Regards,

Joris

[PaulDBrown · Accepted solution]

Try the following (I've created a year table to use as a slicer so you will to adapt the measures to you use case):

To identify the new clients:

New vs previous year =
VAR _Cal =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Table'[StartDate] ), MAX ( 'Table'[EndDate] ) ),
        "@Year", YEAR ( [Date] )
    )
VAR _List =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Table', _Cal ),
            [Date] >= 'Table'[StartDate]
                && [Date] <= 'Table'[EndDate]
        ),
        'Table'[ClientNr],
        [@Year]
    )
VAR _PrevYears =
    FILTER (
        ALL ( 'Year Table' ),
        'Year Table'[Value]
            >= SELECTEDVALUE ( 'Year Table'[Value] ) - 1
            && 'Year Table'[Value] <= SELECTEDVALUE ( 'Year Table'[Value] )
    )
VAR _SelYears =
    CROSSJOIN ( VALUES ( 'Table'[ClientNr] ), _PrevYears )
VAR _MX =
    MAXX ( INTERSECT ( _List, _SelYears ), [@Year] )
RETURN
    IF (
        AND (
            COUNTROWS ( INTERSECT ( _list, _SelYears ) ) = 1,
            _MX = SELECTEDVALUE ( 'Year Table'[Value] )
        ),
        "New"
    )

To get the number:

Number of new clients = 
COUNTROWS(FILTER(VALUES('Table'[ClientNr]), [New vs previous year] = "New")) +0

Can you clarify how you are calculating  "clients (new and old) left for good in those year"?

Sample PBIX file attached

[Joris_NL]

Awesome!

Your example file works perfectly. But applying it to my own dataset gives an error: the Calendar function can't work with blanks.

I tried adding IF(NOT(ISBLANK(...))) but it fails combined with MIN or MAX, as this looks for values, not columns.

Perhaps you can make it work if the dataset looks like this? In which the bottom two clients can be ignored completely for all our calculations (but not for other graphs, so I can't delete rows with a query!). 

ClientNr	StartDate	EndDate
client2	1-1-2017	31-12-2020
client1	1-1-2021	31-12-2021
client3	1-1-2022	1-1-2023
client1	1-1-2022	1-1-2023
client2	1-1-2022	1-1-2023
client4	1-1-2021
client5		1-1-2022

The desired outcome should be the same as before, so the number of new clients:

2020: 0

2021: 1 (so client 1)

2022: 2 (clients 3 and 2, but not 1)

 
Then back to your question to clarify 'clients leaving for good'. I actually meant 'clients not showing up next year'. So that's only Client2 in 2020 in our example (as well as Clients1, 2 and 3 leaving in the year 2023, but we're not going to calculate for future years).

Thanks again and I hope you can help!

[bolfri]

Hi,

this is what I've got so far. Customer2 is considered as a New Customer in 2017 because it was his first StartDate in the dataset. I want to understand the logic why do you want to count him as a New Customer in your data.

[Joris_NL]

Thanks for your help Bolfri!

The reason we want to count Customer 2 as a new customer in 2022 is because the he/she didn't have a relationship in the previous year, i.e. 2021. So his/her date period of 2017 to 2020 doesn't count for measuring the year 2022. He/she is a new customer in both 2017 and in 2022 - though Customer 1 is only new in 2021 despite having multiple records as well.

I'm afraid this complicates our approach. We can't just use the minimum starting date, but have to add columns or make calculations for every measured year. I've made an attempt (see replies) but its not working.

Perhaps the context helps: technically speaking it's not about sales but about mental health care programs. Most of the customers (clients) have a rich history of relationships with the organization (local government). So only if the customer's relationship with the organisation has ended for at least a calendar year, without any new or expanded programs, I consider his/her program 'finished' and the customer 'cured' (to put it simply). If the customer returns after an absence of a calendar year or more, I'd like to count it as a new customer.

I really hope you can help me on this one!

[Joris_NL]

Thanks! I guess that's the sollution although it requires a lot more DAX knowledge than I have.

In my case, it might be a bit easier though. Perhaps just create 3 variables: clients year X, clients year X-1. clients year X-2. clients year X-3. Then determine unique ones (new clients). 

Will post it here if I got it working.

Thanks!

[sjrrkb123]

@Joris_NL 
I have built this into some of my power bi data models. If you follow the directions from SQLBI on New and Returning Customers, you should be able to do this easily.

That said, for very large datasets, you will have much better performance building flags into your dataset that identify the first time a customer interacts with your organization as you can drastically simplify the DAX().

[Joris_NL]

So I'm almost there I think. Can you please have another look? There are some difference with your data set from the example which makes it harder to reproduce the results.

MeasureYear = 2022

And then:

MeasureNewClients = 

VAR ListClntsCurrentYear = 

CALCULATETABLE(

    VALUES(Table[Clientnr]);

    FILTER(Table;[StartDate].[Year] >= [MeasureYear]

    && [StartDate].[Year] < [MeasureYear]+1 -- For simplicity sake I'm leaving out the filters for the ending date, they work practically the same.

    )

)
VAR ListClntsPrevYear = 

CALCULATETABLE(

    VALUES(Table[Clientnr]);

    FILTER(Table;[StartDate].[Year] >= [MeasureYear]-1

    && [StartDate].[Year] < [MeasureYear]

    )

)

VAR NieuwClntBGPeiljaar =

    COUNTX(ClntBGPeiljaar;

    CROSSFILTER(ClntBGPeiljaar;ClntBGPeiljaarVorig;BOTH))   --THIS LINE GOES WRONG. THE CROSSFILTER DOESNT RECOGNIZE THEM AS COLUMNS.

RETURN

    NieuwClntBGPeiljaar