cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## How to count NEW clients in year X (distinct)

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 😞

Regards,

Joris

1 ACCEPTED SOLUTION
Community Champion

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 =
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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

7 REPLIES 7
Community Champion

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 =
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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

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!

Super User

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.

Proud to be a Super User!

Helper I

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!

Helper I

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!

Helper III

@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().

Helper I

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`

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors