The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Folks,
I am trying to obtain the number of uninque values within a measure of distinct values. The goal is to display a stacked column that shows the total number of clients, the distinct number of clients for that year, recurring clients and the number of new clients for each year that did not appear in previous years.
I've been able to display 3 of the 4 items in a single graph, but I am unable to correctly calculate the NEW clients for each year that do not appear in previous years. I toyed around with "IF" statements, counta/x and tables with min dates but cannot figure out how to display this subset of data.
My idea has been on the focus on an "IF" statement that checks for previous instances if a unique client is being referenced in previous years.
The data I am using is very simple. Client Name and Date (with hierarchy).
Client name Date
A-client January 2000
A-client January 2010
A-client January 2021
B-Client January 2005
B-Client January 2015
B-Client January 2021
C-Client January 2021
D-Client January 2000
D-Client January 2005
D-Client January 2015
D-Client January 2021
The results should have counted each NEW client for each year by comparing the current date with previous dates.
2000 2005 2010 2015 2021
2 1 0 0 1
Solved! Go to Solution.
Hi @kbruce ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column to get the Year
Year = YEAR('Table'[Date])
2. Create two measures as below to get the count of new clients
Measure =
VAR _minyear =
CALCULATE ( MIN ( 'Table'[Year] ), ALL ( 'Table'[Year] ) )
VAR _curyear =
SELECTEDVALUE ( 'Table'[Year] )
VAR _preclients =
CALCULATETABLE (
DISTINCT ( 'Table'[Client name] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] < _curyear )
)
VAR _curclients =
CALCULATETABLE (
DISTINCT ( 'Table'[Client name] ),
FILTER ( 'Table', 'Table'[Year] = _curyear )
)
RETURN
IF (
_curyear = _minyear,
DISTINCTCOUNT ( 'Table'[Client name] ),
COUNTROWS ( EXCEPT ( _curclients, _preclients ) )
)
Count of new clients = SUMX ( VALUES ( 'Table'[Year] ), [Measure] )
Best Regards
I'm almost there. I did not take into consideration multiple instances of a client in the same year, but @v-yiruan-msft did. I believe that I am all set with both of these replies.
Upon further review/tinkering, @v-yiruan-msft solution is not correct and does not solve the last part of what is needed.
Client A is listed multiple times in the same year @FrankAT is closer, but the measure calculating the column adds a 1 next to each of the duplicates, below. I need to remove the duplicates to obtain an accurate count.
A-client January 2000
A-client January 2000
A-client January 2000
Hi @kbruce ,
As checked the table data in your first post, these three clients A-client are from different years(2000,2010 and 2021) separately. So the client A-client is not duplicate value in same year... Could you please provide some sample data with duplicated clients and your actual expected result with correct calculation logic and specific examples. Thank you.
Best Regards
Hi @kbruce ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column to get the Year
Year = YEAR('Table'[Date])
2. Create two measures as below to get the count of new clients
Measure =
VAR _minyear =
CALCULATE ( MIN ( 'Table'[Year] ), ALL ( 'Table'[Year] ) )
VAR _curyear =
SELECTEDVALUE ( 'Table'[Year] )
VAR _preclients =
CALCULATETABLE (
DISTINCT ( 'Table'[Client name] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] < _curyear )
)
VAR _curclients =
CALCULATETABLE (
DISTINCT ( 'Table'[Client name] ),
FILTER ( 'Table', 'Table'[Year] = _curyear )
)
RETURN
IF (
_curyear = _minyear,
DISTINCTCOUNT ( 'Table'[Client name] ),
COUNTROWS ( EXCEPT ( _curclients, _preclients ) )
)
Count of new clients = SUMX ( VALUES ( 'Table'[Year] ), [Measure] )
Best Regards
Hi @kbruce ,
first create two new calculated column, one with year number the other like this:
Use the following measure in a matrix:
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |