Finding earliest date given a value in another column

[npombo]

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.

[npombo]

Hi Jay, this worked! Thank you for assisting me.

[Anonymous · Accepted solution]

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

[amitchandak]

@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])