Get latest date of each year

[huihui]

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!

[v-ljerr-msft · Accepted solution]

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

[huihui]

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

[v-ljerr-msft]

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