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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AnthonyJoseph
Resolver III
Resolver III

check if item exists in the latest year

Hello,

 

I have a table visual and would like to display if the item that exists in the latest year (latest year in the data selected) by a measure.

 

Below is the sample data and expected output:

Unit table:  
ItemYearunit
Fruit2022100
Fruit2021200
Fruit2020500
Veggies2021100
Veggies202040
Meat2022150
Meat2021250

 

Expected output:  Here "Item exist in the latest selected year" is the output measure that will say whether the Item exists in the latest year (here 2022 is the latest or maximum year). If the item exists in the latest year then it should return "Yes" else if the item is not there in the latest year then it should be "No".

 

ItemYearunitItem exist in the latest selected year
Fruit2022100Yes
Fruit2021200Yes
Fruit2020500Yes
Veggies2021100No
Veggies202040No
Meat2022150Yes
Meat2021250Yes

 

Please can you let me know how to achieve this result.

 

Thanks,

AnthonyJoseph

1 REPLY 1
vicky_
Super User
Super User

Hello!
Try something like:

Exist in latest year = 
var latest = CALCULATE(MAX('Table'[Year]), ALLSELECTED('Table'))
var itemsInLatestYear = SELECTCOLUMNS(FILTER(ALL('Table'), 'Table'[Year] = latest), "Items", 'Table'[Item])
return IF(SELECTEDVALUE('Table'[Item]) in itemsInLatestYear, "Yes", "No")

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.