Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Joris_NL
Helper II
Helper II

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:

 

ClientNrStartDateEndDate
client21-1-201731-12-2020
client11-1-202131-12-2021
client31-1-20221-1-2023
client11-1-20221-1-2023
client21-1-20221-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

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
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 =
    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

 

New.gif

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

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
PaulDBrown
Community Champion
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 =
    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

 

New.gif

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

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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!). 

 

ClientNrStartDateEndDate
client21-1-201731-12-2020
client11-1-202131-12-2021
client31-1-20221-1-2023
client11-1-20221-1-2023
client21-1-20221-1-2023
client41-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
Super User
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.

bolfri_1-1671726706386.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Helper II
Helper II

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
Helper III
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().

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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