Forum Discussion
Finding earliest date given a value in another column
Hello,
I am having trouble creating a measure that takes the earliest date based on a value in another column. To break it down in simpler terms, let's call the referenced column "Status" and the two possible values are "Yes" or "No", and in this case we are filtering for "Yes". The date column will be called "date".
So far, I've been successful with writing a measure that takes the earliest date column using the MIN function:
Measure1 = MIN('Table Name'[Date])
I've tried incorporating the Status = "Yes" component of the measure as well with no luck. Here are some instances I've tried:
Measure2 = MIN(FILTER('Table Name', 'Table Name'[Status] = "Yes"))
Measure3 = MIN('Table Name'[Date], FILTER 'Table Name', 'Table Name'[Status] = "Yes")
Measure4 = FILTER('Table Name', 'Table Name'[Status] = "Yes", MIN('Table Name'[Date]))
Perhaps the FILTER function isn't the way to go here. Any feedback is appreciated.
- Anonymous4 years ago
Hi npombo ,
Please use calculate() function to wrap the filter() formula.
For example:
measure = calculate(min([date]),filter(allselected('table'),[status]="Yes"))
Best Regards,
Jay
3 Replies
- amitchandakSuper User
npombo , try like
Measure2 = MINX(FILTER(allselected('Table Name'), 'Table Name'[Status] = "Yes"), 'Table Name'[Date])
orr
Measure2 = MINX(FILTER(('Table Name'), 'Table Name'[Status] = "Yes"), 'Table Name'[Date])
- npomboHelper I
Hi Jay, this worked! Thank you for assisting me.
- AnonymousNot applicable
Hi npombo ,
Please use calculate() function to wrap the filter() formula.
For example:
measure = calculate(min([date]),filter(allselected('table'),[status]="Yes"))
Best Regards,
Jay