March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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.
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.
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.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Thanks for your reply. I am looking to return the minumum year, not the project with that has the minum year.
User | Count |
---|---|
122 | |
99 | |
89 | |
73 | |
65 |
User | Count |
---|---|
138 | |
115 | |
115 | |
98 | |
98 |