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.
Hello, I want to show the count of new clients and existing clients (by using cards) and filtered by month (using slicer), and here is an example of the issue.
Sample data:
Date | Client | Client Type (Calculated Column) |
1/1/2020 | A | New Client |
2/1/2020 | B | New Client |
1/2/2020 | A | Existing Client |
4/2/2020 | C | New Client |
5/2/2020 | C | Existing Client |
The cards are showing:
Number of new clients in February = 1
Number of existing clients in February = 2
The result I want:
Number of new clients in February = 1
Number of existing clients in February = 1 (because Client C is considered a new client in February)
Much appreciated if anyone could help.
Solved! Go to Solution.
Try this:
Existing Clients =
var _existing = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "Existing Client")
var _new = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "New Client")
return COUNTROWS(EXCEPT(_existing, _new))
New Clients =
CALCULATE(
DISTINCTCOUNT('Client List'[Client]),
'Client List'[Client Type (Calculated Column)] = "New Client"
)
Bless you!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
@Anonymous You could try this calculated column:
Client Type =
IF (
ISBLANK (
CALCULATE (
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Client] ),
'Table'[Date] < EARLIER ( 'Table'[Date] )
)
)
|| DATEDIFF (
CALCULATE (
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Client] ),
'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Date],
MONTH
) = 0,
"New Client",
"Existing Client"
)
As as for the measures:
Existing Clients = CALCULATE(DISTINCTCOUNT('Table'[Client]),'Table'[Client Type]="Existing Client")
New Clients = CALCULATE(DISTINCTCOUNT('Table'[Client]),'Table'[Client Type]="New Client")-[Existing Clients]
Edit: Just noticed the requirement that if a client has 2 dates in the same month, they should still be considered a new client. I've adjusted the calculated column.
Edit 2: What you may be after is actually the gain/loss pattern. You can read about it here: https://www.daxpatterns.com/new-and-returning-customers/
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Thank you, the link is really useful!! 🙂
You can do it by
1- Add Client Type as Visual Filter to show only New or Existing Client
2- You can create a measure that @amitchandak created.
Proud to be a Super User!
@Anonymous , I am assuming the date format is dd/mm/yyyy
Try measures like
distinctcount(table[Client])
calculate(distinctcount(table[Client]),[Client Type] ="New Client")
calculate(distinctcount(table[Client]),[Client Type] ="Existing Client")
Hi @amitchandak , thanks for your reply but what I want to show is
Number of new clients in February: 1 (Client C)
Number of existing clients in February: 1 (only Client A, no matter how many times client C appears in February is still considered as a new client)
Much appreciated.
Hello @Anonymous
You may try the following measures:
New Client =
CALCULATE (
DISTINCTCOUNT ( 'Client List'[Client] ),
'Client List'[Client Type (Calculated Column)] = "New Client"
)
Existing Client =
VAR _Summerize =
SUMMARIZE (
'Client List',
'Client List'[Client],
'Client List'[Client Type (Calculated Column)]
)
VAR _Existing =
SUMX (
FILTER (
_Summerize,
'Client List'[Client Type (Calculated Column)] = "Existing Client"
),
1
)
VAR _New =
SUMX (
FILTER (
_Summerize,
'Client List'[Client Type (Calculated Column)] = "New Client"
),
1
)
VAR _Check =
IF ( _New < _Existing , _Existing - _New, _New- _Existing )
RETURN
_Check
Output:
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Hi @vivran22, thank you so much, this really solved the problem, but when the new client only appears once in a month, the _existing will still minus _new when it not supposed to minus it.
For example:
Date | Client | Client Type (Calculated Column) |
1/1/2020 | A | New Client |
2/1/2020 | B | New Client |
1/2/2020 | A | Existing Client |
4/2/2020 | C | New Client |
5/2/2020 | C | Existing Client |
1/3/2020 | D | New Client |
2/3/2020 | A | Existing Client |
3/3/2020 | C | Existing Client |
With the measure, you provided it will show
Number of new clients in March = 1
Number of existing clients in March = 1 (it supposed to be 2 but with the _check equation, it will minus the number of new clients)
Is there any way that I could compare the [client with the condition of client type = new] and [client with the condition of client type = existing]?
Thanks,
@Anonymous is it safe to assume that the date of New Client will always be less than Existing client for the same client ID?
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Try this:
Existing Clients =
var _existing = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "Existing Client")
var _new = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "New Client")
return COUNTROWS(EXCEPT(_existing, _new))
New Clients =
CALCULATE(
DISTINCTCOUNT('Client List'[Client]),
'Client List'[Client Type (Calculated Column)] = "New Client"
)
Bless you!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |