Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PowerBI87
Frequent Visitor

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. 

 

ProjectDelivery YearEarliest Year
X2018Yes
Y2019No
Y2018Yes
A2016Yes
B2020Yes
C2018No
C2017Yes
C2019No
X2019No

 

Please advise,

Thank you

1 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.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PowerBI87
Frequent Visitor

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.

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.