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.
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.
Community SupportHi 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.