check if item exists in the latest year

AnthonyJoseph — Tue, 01 Aug 2023 21:56:44 GMT

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:
Item	Year	unit
Fruit	2022	100
Fruit	2021	200
Fruit	2020	500
Veggies	2021	100
Veggies	2020	40
Meat	2022	150
Meat	2021	250

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

Item	Year	unit	Item exist in the latest selected year
Fruit	2022	100	Yes
Fruit	2021	200	Yes
Fruit	2020	500	Yes
Veggies	2021	100	No
Veggies	2020	40	No
Meat	2022	150	Yes
Meat	2021	250	Yes

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

Thanks,

AnthonyJoseph

Re: check if item exists in the latest year

vicky_ — Tue, 01 Aug 2023 23:10:08 GMT

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")

topic check if item exists in the latest year in DAX Commands and Tips

check if item exists in the latest year

Re: check if item exists in the latest year