cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
admin11
Post Prodigy
Post Prodigy

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 III
Super User III

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 IV
Super User IV

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

 

Or create a column to filter 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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!


View solution in original post

@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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.