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

Highlighted
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

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors