Forum Discussion
Return Earliest Year
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
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.
6 Replies
Thanks for your reply. I am looking to return the minumum year, not the project with that has the minum year.
Is there an if statement that can display a "yes" in the specific row that is the first year?
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.Thank you Liang, this works.
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.
