Return Earliest Year

[PowerBI87]

HI Experts,

I am trying to have powerBI return the third column below:

It has to filter Project column, find all projects with the same name, and then return a yes for the earliest year. 

Project	Delivery Year	Earliest Year
X	2018	Yes
Y	2019	No
Y	2018	Yes
A	2016	Yes
B	2020	Yes
C	2018	No
C	2017	Yes
C	2019	No
X	2019	No

Please advise,

Thank you

[Ashish_Mathur]

Hi,

Try this calculated column formula

=IF(Data[Delivery Year]=CALCULATE(Min(Data[Delivery Year]),Filter(Data,Data[Project]=Earlier(Data[Project]))),"Yes","No")

Hope this helps.

[PowerBI87]

Is there an if statement that can display a "yes" in the specific row that is the first year?

[V-lianl-msft · Accepted solution]

Hi @PowerBI87  ,

You can also try using the allexcept function to calculate the minimum year for each project and compare it with the year of the current line.Try this measure:

Measure =
VAR min_year =
    CALCULATE (
        MIN ( 'Table'[Delivery Year] ),
        ALLEXCEPT ( 'Table', 'Table'[Project] )
    )
RETURN
    IF ( MAX ( 'Table'[Delivery Year] ) = min_year, "Yes", "No" )

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

[PowerBI87]

Thank you Liang, this works.

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[PowerBI87]

Thanks for your reply. I am looking to return the minumum year, not the project with that has the minum year.