Button to filter data to 5 years prior than current date

[U156531]

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

[U156531]

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

[MaheshkumarSuma]

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)

[U156531]

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?

[MaheshkumarSuma]

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?

[U156531]

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? 

[MaheshkumarSuma]

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 🙂

[U156531 · Accepted solution]

Thanks for your help!

[MaheshkumarSuma]

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"