Earliest for a text column of year, per plant

[Unicorn_Tech]

Hi.  I have two columns - one of plant varieties, and one of year planted. Many rows, all detailing farm name, area planted, etc.

What I want is a final filter in my report that shows the earliest year the variety was planted.  The final result will simply be year, and variety.

I understand the DAX function "Earliest" can return the earliest date, but I don't want the earliest date by report, but the earliest date for the variety listed in the table.

Or, I want a final filter on the report side, that will show the earliest year.

THis is further complicated by the fact that this year is a text field (2014, 2015, etc.)

Please advise the best solution.  Thank you!

[v-yuta-msft]

@Unicorn_Tech ,

Suppose you have a fact date table and a plant data table. And a slicer based on year column in plant date table, you may create a measure using DAX like pattern below:

Result =
CALCULATE (
    MIN ( Plant[Year] ),
    FILTER (
        ALLSELECTED ( Plant[Year] ),
        Plant[Date] IN VALUES ( Fact_Date[Date] )
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Unicorn_Tech]

Hi.  THank you for replying.  Based on yoru formula, I tried this, but it's returning no values:

First Year =

CALCULATE (

MIN ( new_plotinformations[new_year_of_planting_grafting] ),

FILTER (

ALLSELECTED ( new_plotinformations[new_year_of_planting_grafting]),

new_plotinformations[Variety Name] IN VALUES (new_plotinformations[new_year_of_planting_grafting] )

)

)

My text field of year is in the same table as the plant varieties.  Can you advise what I did wrong?

[Unicorn_Tech]

HI again,

Would there also be a simple way to do a filter on the report to indicate "Top 1" per year?  If it's a text field?

[Unicorn_Tech]

I tried this solution, and I was suprised it didn't work:

I created a column that converted the year to a whole number.

I then set up a filter to show "Bottom N" on the table.  Sometimes it pulled the earliest year per variety, other times it pulled the latest or middle.  

[v-yuta-msft]

@Unicorn_Tech ,

Could you please share some sample data for further analysis?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.