Current Year and Current month in a slicer

[MSchultz93]

Hello,

I have a problem with selections in a slicer. 

In my slicer I have the column calendar[Year], it contains 2021,2022,2023,2024,2025,2026. I want the current year to be selected, however, all years should be visible in the slicer.

The problem is when I create a calculated column where IsCurrentYear = if(Max(calendar[Year])=Year(Today()),1,0) and I select IsCurrentYear = 1 as a filter in the slicer only the current year appears. I want the current year to be selected but all others should appear.

Thank you for the help

[shafiz_p]

Hi @MSchultz93 To accomplish this create a year table with all distinct year values in power query. Sort year in ascending order. Create an index column from 0.  Create a currentYear custom column using the formula shown in the image below.

Now recalculate Index column using the formula shown in the below:

Now change type of sort index to whole number and CurrentYear to text. Remove Index column. See Image:

Load data to power bi. Create on to many relationship using year column of the above table to calendar table year column.

Now sort the newly created Current Year column by sort index column. See image below:

Go to report page, create slicer and place the newly created current year column in values section. See image below:

Hope this helps!!

If this solved your problem, Please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[MAwwad]

1. Create the Measure:

   IsCurrentYear = IF(YEAR(TODAY()) = MAX('Calendar'[Year]), 1, 0)

2. Add the Slicer:

   • Add a slicer to your report and use the Calendar[Year] column.

   • Create the Bookmark:

     • Select the current year in the slicer.
     • Go to the View tab and open the Bookmarks pane.
     • Add a new bookmark and name it "Default Year".
     • Ensure that the bookmark captures the slicer state by checking the "Data" option in the bookmark settings.

     • Add the Button:

       • Insert a button into your report.
       • Go to the Action properties of the button.
       • Set the action type to Bookmark.
       • Select the "Default Year" bookmark.

[MFelix · Accepted solution]

Hi @MSchultz93 ,

You cannot have the selection of the current year based on a filter since it will only return the selected value.


The pre-selected value of a slicer can only be done by selecting the value and when you publish it that will be the default selection, or using a bookmark that will force that specific slicer to select the current year. however in both case if the year changes you need to go back and change it to the new year.

For this default selection of current year I usually create a column with the text Current Year and the rest of the years as numbers then I place the default value for that current year on the slicer so when the year changes the default selectionn get updated.