Forum Discussion
Counting distinct items based on date criteria
Hi Anonymous
Thanks for reaching out to us.
Try this measure
count = COUNTROWS(FILTER('Table','Table'[NextDate] < TODAY() && 'Table'[NextDate] = CALCULATE(MAX('Table'[NextDate]),ALLEXCEPT('Table','Table'[Name]))))I also create a sample for your reference, see file attached bellow.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionAnonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Thanks for the response, I will try to provide more context.
So the data is set out in this format:
| Name | Date | NextDate |
| Staff 1 | 7/1/21 | 10/1/21 |
| Staff 1 | 10/1/21 | 1/1/22 |
| Staff 2 | 6/1/21 | 9/1/21 |
| Staff 2 | 9/1/21 | 12/1/21 |
| Staff 3 | 7/1/21 | 10/1/21 |
| Staff 3 | 3/1/21 | 6/1/21 |
What I am needing to do is find the highest date in the 'NextDate' for each unique name in the 'Name' column. So the formula will identify the following:
| Name | NextDate |
| Staff 1 | 1/1/22 |
| Staff 2 | 12/1/21 |
| Staff 3 | 6/1/21 |
From there, I need it to identify which of the dates in the 'NextDate' column have already passed and count them to display in a data card. So with the data above, it would put "1" in the data card, since the only 'NextDate' value that has passed is the one for Staff 3.
Thank you, it is greatly appreciated.
v-xiaotangCommunity Support
Hi Anonymous
Thanks for reaching out to us.
Try this measure
count = COUNTROWS(FILTER('Table','Table'[NextDate] < TODAY() && 'Table'[NextDate] = CALCULATE(MAX('Table'[NextDate]),ALLEXCEPT('Table','Table'[Name]))))I also create a sample for your reference, see file attached bellow.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.