Forum Discussion
Counting distinct items based on date criteria
Greetings,
I am having an issue with figuring out a measure that I just cant seem to hammer out. I am very new with using DAX.
I am needing to count rows based on dates, essentially something like:
Measure = CountRows(
Filter(
Table, Table[NextDue].Date <= Today()))
However, the table it is counting contains duplicate values such as:
What I am needing this formula to do is identify the most recent date in the 'NextDue' column for each distinct name in the 'Name' column.
For instance, if there were 5 different names in the 'Name' column each with 3 items that all containt different dates it would identify which date is the latest for that distinct 'Name' and then determine whether it is less than Today(). If it is, it counts that item.
I hope this is clear enough, I am not 100% on all the PowerBI jargon.
Thanks for any help!
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.
4 Replies
Anonymous 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.
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.
Hi Anonymous
I tried to solve the problem based on my understaing. If this is not the requirement, please post more details as Greg_Deckler mentioned below.
The below measure will get the count of items which has dates less than today.
m_count1 = CALCULATE( COUNT(Table1[Client]), FILTER(Table1,Table1[Date]<TODAY()), ALLEXCEPT(Table1,Table1[Client]) )Here is teh result for the sample dataset:
Thanks
Raj
