Forum Discussion

npombo's avatar
npombo
Helper I
4 years ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    4 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

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

  • Anonymous's avatar
    Anonymous
    Not 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