Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
But not the formula itself to calculate 😞
Please help lol!
Regards,
Joris
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
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
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!).
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!
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!
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!
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!
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
161 | |
129 | |
129 | |
92 | |
91 |