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.
I have a calendar slicer for user input which does not have a relationship to my data table. User will select a minimum and maximum date to form a range such a 1/1/2018 - 1/31/2018
I have table for client #s with start and end dates. The same # can have multiple start and end dates.
I want to find "new" clients who started service within the date range. The complication is that I want to count a client as long as they began service after the start date and did not have a service that was ongoing at the start of the period. So service in previous periods are ignored.
Some sample data to illustrate:
ClientID | Start date | End date |
1 | 12/30/2017 | 1/5/2018 |
1 | 1/10/2018 | 1/18/2018 |
1 | 1/28/2018 | 2/2/2018 |
2 | 12/1/2017 | 12/12/2017 |
2 | 1/5/2018 | 1/12/2018 |
3 | 1/12/2018 | 1/19/2018 |
3 | 1/27/2018 | 3/3/2018 |
4 | 11/5/2017 | 1/21/2018 |
4 | 2/3/2018 | 2/10/2018 |
5 | 12/20/2017 | 2/2/2018 |
With this data, the correct count would be 2 (clients #2 & 3).
I can filter the table down to relevant services in the period via:
Hi ThaddeusB,
"I want to find "new" clients who started service within the date range. The complication is that I want to count a client as long as they began service after the start date and did not have a service that was ongoing at the start of the period. So service in previous periods are ignored."
<--- Your requirement is not so clear. For example, could you please clarify why clientID 1 doesn't meet the condition. Could you give more details about the logic?
Regards,
Jimmy Tao
@v-yuta-msft wrote:Hi ThaddeusB,
"I want to find "new" clients who started service within the date range. The complication is that I want to count a client as long as they began service after the start date and did not have a service that was ongoing at the start of the period. So service in previous periods are ignored."
<--- Your requirement is not so clear. For example, could you please clarify why clientID 1 doesn't meet the condition. Could you give more details about the logic?
Regards,
Jimmy Tao
@v-yuta-msft Thanks for your reply. I would be happy to (try to) clarify. A "new" client is one who started a service after the start date of the period and didn't also have a service ongoing at the start of the period. Client 1 does not qualify as new in January because he had a service from 12/30/17-1/5/18.
One possible logic would be
1) Filter down to services in the selected period (using filter I posted or something similar):
ClientID | Start date | End date |
1 | 12/30/2017 | 1/5/2018 |
1 | 1/10/2018 | 1/18/2018 |
1 | 1/28/2018 | 2/2/2018 |
2 | 1/5/2018 | 1/12/2018 |
3 | 1/12/2018 | 1/19/2018 |
3 | 1/27/2018 | 3/3/2018 |
4 | 11/5/2017 | 1/21/2018 |
5 | 12/20/2017 | 2/2/2018 |
2) Find the minimum entry date by client in the filtered set
ClientID | Start date | End date |
1 | 12/30/2017 | 1/5/2018 |
2 | 1/5/2018 | 1/12/2018 |
3 | 1/12/2018 | 1/19/2018 |
4 | 11/5/2017 | 1/21/2018 |
5 | 12/20/2017 | 2/2/2018 |
3) Count the number of minimum entry dates on or after the selected period start date.
ClientID | Start date | End date |
2 | 1/5/2018 | 1/12/2018 |
3 | 1/12/2018 | 1/19/2018 |
So I came up with the following that appears to work:
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |