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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
admin11
Memorable Member
Memorable Member

Year = year([Date]) How to modify to show last 5 year ?

Hi All

 

May i know how to modify the below expression :-

Year = year([Date])
So that it will only show last 5 year.
 
 
admin11_0-1614255346623.png

 

 

 

 

1 ACCEPTED SOLUTION

@admin11 

 

Download your PBIX with solution

 

Hi Paul,

Actually there's a better way.

In the Slicer, use Date from the Date Table in the Field and set it to only display the Year.

Then in your Filters use TopN filtering and drag Date from the Date Table into the By Value area and select to show the Latest Date, then set the filter to show the Top 5.

last5.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

7 REPLIES 7
PhilipTreacy
Super User
Super User

Hi @admin11 

Try this

Last 5 Years = FILTER(Year, YEAR(Date[Year]) >= 2018)

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


amitchandak
Super User
Super User

@admin11 , you can create a measure. Or user slicer

 

Or create a column to filter 

FLag = if(year([Date]) >= year(Today()) -5, 1,0)

@amitchandak 

@PhilipTreacy 

Can you please share with me how to create expression for year , so when i use at Slicer , the Year filter will only display last 5 year.

Paul Yeo

@admin11 

 

Download your PBIX with solution

 

Hi Paul,

Actually there's a better way.

In the Slicer, use Date from the Date Table in the Field and set it to only display the Year.

Then in your Filters use TopN filtering and drag Date from the Date Table into the By Value area and select to show the Latest Date, then set the filter to show the Top 5.

last5.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


@PhilipTreacy 

Now i start to understand that , need to make use of filter panel. thank you for sharing.

Paul

@amitchandak 

 

Your expression working fine. But i al looking for expression Year to display at every page user don't to tick 1 or 0. 

 

@PhilipTreacy  approach is correct , but got error.

https://www.dropbox.com/s/11dzrqm4g56zqys/PBT_V2021_333%20year%20display%202020%202021.pbix?dl=0

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.