How to have the last five years checked in a slicer visual as default but still show all the years?

[D_PBI]

Hi,
I have a slicer visual (vertical list) that contains each year in my model. It currently dates from 1960 to 2055 and so each year between those dates is available to check on the slicer.
Is there a way where I can check the boxes for the current year and four previous years (i.e. 2020 - 2024) but still have all the other years showing but just unchecked?  And as next year (2025) comes around, the slicer visual will default to checking only the years 2021-2025?
Thanks.

[v-jialongy-msft · Accepted solution]

Hi @D_PBI 

I have carefully considered the method you envisioned, and found that it is not feasible, and I can provide you with an alternative method

First, I created the following table

You can then construct your slicer using the following calculated columns

IsSelectedYear =

IF(

    YEAR(TODAY()) - 'Table'[date].[Year] <= 4 && YEAR(TODAY()) - 'Table'[date].[Year] >= 0,

    "Selected",

    "Not Selected"

)

This formula can filter data from five years based on your current time

This is the result you want

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[D_PBI]

@v-jialongy-msft - thank you for your efforts but that isn't the end result I was hoping for. I also need all the other years present in the slicer visual but just unchecked.

[v-jialongy-msft]

 Hi @D_PBI 

The PBIX file I provided contains unchecked years, but I used a filter to hide them, and if you want to show all the years just unchecked, you can also drag the year field into the slicer.

This is the result you want.

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[D_PBI]

@v-jialongy-msft  Thanks for your follow up. Apologies, I didn't fully view your .pbix file the first time around. I see what you've done now. I guess by checking the 'Selected' parent chechbox, as each year turns the current year will automatically be checked as it falls within the 'Selected' year range.

[lukiz84]

not possible.