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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


@ 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!:
Mastering Power BI 2nd Edition

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.