cancel
Showing results for
Did you mean:
Regular Visitor

## Get latest date of each year

Hi all, i would like to retrieve the data of the latest date of every year.

Below is an example of what i want to achieve:

MyTable:

After:

Hopefully someone can guide me through. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Get latest date of each year

Hi @huihui,

Based on my test, you should be able to follow steps below to get your expected result.

1. Use the formula below to add a calculate column in your table to indicate if the current date if the latest date of its year.

```IsLatestDateofYear =
VAR currentYear =
YEAR ( MyTable[date] )
VAR latestDateofYear =
CALCULATE (
MAX ( MyTable[date] ),
FILTER ( ALL ( MyTable ), YEAR ( MyTable[date] ) = currentYear )
)
RETURN
IF ( MyTable[date] = latestDateofYear, 1, 0 )
```

2. Then you should be able to use this calculate column as a Slicer on the report to retrieve the data of the latest date of every year.

Regards

3 REPLIES 3
Microsoft

## Re: Get latest date of each year

Hi @huihui,

Based on my test, you should be able to follow steps below to get your expected result.

1. Use the formula below to add a calculate column in your table to indicate if the current date if the latest date of its year.

```IsLatestDateofYear =
VAR currentYear =
YEAR ( MyTable[date] )
VAR latestDateofYear =
CALCULATE (
MAX ( MyTable[date] ),
FILTER ( ALL ( MyTable ), YEAR ( MyTable[date] ) = currentYear )
)
RETURN
IF ( MyTable[date] = latestDateofYear, 1, 0 )
```

2. Then you should be able to use this calculate column as a Slicer on the report to retrieve the data of the latest date of every year.

Regards

Regular Visitor

## Re: Get latest date of each year

Omg thanks it works! But i am not sure why when i use that column to as a filter, i show no results though.

Microsoft

## Re: Get latest date of each year

Hi @huihui,

If you don't want to show that column as a Slicer on the report, you can also use it to apply a visual/page/report level filter to get the same result. For more details about how to apply a visual/page/report level filter, you can refer to this article.

Regards

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors