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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
U156531
Helper V
Helper V

Button to filter data to 5 years prior than current date

I need to add a button to my pbix that will filter all the data to 5 years older than the current date. Can someone show me how to accomplish this?

Thanks

1 ACCEPTED SOLUTION

Thanks for your help!

View solution in original post

8 REPLIES 8
U156531
Helper V
Helper V

The button doesn't seem to be filtering the data. Did I miss a step?

 

U156531_0-1724858100931.png

 

The one part that's missing is applying the measure as a filter. After creating the DAX Measure, you need to apply it as filter on your visual. 

( Drag the measure into the filter pane of the visual and set the filter condition to show IsOlderThanFiveYears = 1)

That worked, I got all the years prior to 5 from the current date. Now I also want to be able to choose specific years or all the years including the current year. The slicer that I have for the Year isn't working now that I have the Older than 5 Years button. Is it possible for me to have the button and the Year slicer?

U156531_0-1724868923340.png

 

So you want to be able to view the data for the last 5 years for the year you select and not just the current year?



I think I have this worked using two buttons/bookmarks. However I have one onther question. I have the report set up to refresh every night. Each morning when I select the Older than 5 Years button will the refreshed data be reflected in the report? The booksmarks aren't static data correct? 

U156531_0-1724875056496.png

 

 

Yes, correct! The bookmarks in Power BI are not static. They capture the current state of your report, including the filters and slicers at the time they were created. When you refresh your data each night, the bookmarks will reflect the updated data the next morning.

So, when you select the "Older than 5 Years" button after the refresh, it will apply that filter to the most recent data available in your report.
Feel free to hit Accept as Solution if this conversation has helped 🙂


Thanks for your help!

IsOlderThanFiveYears = IF( MAX(YourTable[YourDateColumn]) <= EDATE(TODAY(), -60), 1, 0 )

1. In the Modeling tab, create a New Measure using the above DAX. Use this measure to filer data in your desired visual/report.

2. Go to Report View >> Insert tab >> Button

3. In the Visualizations Pane, go to Actions toggle to ON. Set Type as Bookmark.
4. Add a bookmark and name "Olderthan5Years"

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors