Set Default Slicer Value to Current Month in Power BI

You have a report and want to show a default slicer value that moves with time. When you arrive at the next month, you want the slicer to move to the next month too. How is that possible?

Reports often have slicers that filter a report to show the desired period. In financial reports, you often want to see the last month that completely finished. Let’s say you want to see the month of September. You select September as the slicer value and publish your Power BI report. Then time passes, and you arrive in the next month. The slicer still shows the month of September.

Yet you now want to see the next month, October. Ideally, you would always see the last completely finished month, without making any manual changes to the report. So how can you achieve that?

Table of contents

• 1. Relative Date Filters
  • 1.1. Configure Filters
  • 1.2. Disadvantages
• 2. Set Default Slicer Value
  • 2.1. Create Adjusted Month Column
  • 2.2. Advantages Default Slicer Value
  • 2.3. Configure Slicers
  • 2.4. Create Selected Period Header
  • 2.5. Create Adjusted Year Column
• Conclusion

There are two ways you can look into it.

1. Relative Date Filters

The first approach makes use of the built-in filter types in Power BI. In any report, when you open the report canvas, you can find the filter pane on the right hand. This filter pane allows you to apply filters to your report. You can choose to filter all pages, this page or this visual. When you then move a date field into any of these filters, you can make use of the relative date and relative time filter types.

1.1. Configure Filters

You can do that by:

1. Move a date field into one of the filters (all pages, page or visual filter)
2. Select the Filter type drop-down -> select Relative date.

After selecting a relative date, you can choose to show items in different timeframes.

Any of the above-mentioned options looks at the current date and, from there, shifts the period. When you select input is in the last 1 calendar months, you achieve your goal.

1.2. Disadvantages

This approach has several downsides compared to using a regular slicer panel:

1. you are limited to the options provided by the relative date filters. When you have more specific wishes to filter a period, you will have to look for alternatives.
2. It is inconvenient to apply filters selectively. You either have to apply the filters to all pages, configure it for each page or for each visual. To apply the filters to all pages except one would involve opening each page and manually configuring the filters. Imagine doing this for 20 pages. Yet, creating a filter pane with slicers allows you to sync your filters across multiple pages easily.
3. The filter pane is a hidden and arduous way to adjust periods. Imagine your slicer has a default value, but you want to change it to a different month. You would have to open the filter pane, remove the date filter, find a filter for the relevant month and apply it. And reverse these steps to get back to the default view.
4. It looks unattractive when the filter pane contains many filters. And there are no visual options to adjust the layout of the filter pane. A more attractive option is to have slicers available in a slicer pane. You can decide on the right format for the slicer. You may choose a timeline, dropdown, or search bar and can select a vertical or horizontal orientation.
5. You cannot apply a measure filter on the values in the filter panel. Yet, the pane with slicers allows you to pre-filter using a measure value.

As you can imagine, my preferred method is to use the regular slicers in Power BI, which is what comes next.

2. Set Default Slicer Value

A more flexible way to set a default slicer value in Power BI is by using slicers. Any slicer value that you save in a slicer, remains the same. Putting in a month like September will, therefore always remain the same. Yet, as you have seen in approach 1, inputting something dynamic like the last x months makes the naming dynamic.

The challenge is, how can you create that for a regular slicer? The answer is by adjusting the calendar in Power Query. How? Buckle your seatbelts, we’ll dive right in.

We will use the below calendar for the example. The calendar only shows the first 2 days of each month to make it fit in a single picture.

2.1. Create Adjusted Month Column

Your requirement is to have a month slicer with a default value that moves with the months. And you want this month to be the last completely finished month. You will achieve this by adding a column named Month Name Default to your Date Table.

This column contains the regular Month Names, the only difference being that the last completely finished month will be replaced by the value Default. With each passing of the months, this value moves along.

You will need to create logic to check each month in your calendar against the latest completed month. To get there, I strongly recommend you create a separate column. You may still need the regular Month Name column for other purposes in your report.

For example, to display which month name is displayed. You can then reference the original column, instead of the adjusted one.

So, what steps do you go through to get there?

You first retrieve the current date on your system. Power Query has a function to retrieve the current datetime value on your system, which is DateTime.LocalNow. First, add the current datetime to a calculate column by writing:

= DateTime.LocalNow()

This returns a DateTime value. For date comparisons, you want this value to be in a date format. Adjust the previous formula by using either the Date.From function or the DateTime.Date function:

= Date.From( 
     DateTime.LocalNow() 
  )

= DateTime.Date( 
     DateTime.LocalNow() 
  )

The result of this is a date value with the date on your current system.

The next step is to find the previous month. You can do that by retrieving the current month’s number and subtracting 1 from it. The function Date.Month returns the month number of a date. Then, after retrieving this value, you subtract 1 from it.

= Date.Month(
     DateTime.Date( 
          DateTime.LocalNow() 
     ) 
  )
 - 1

With these steps, you arrive at the month you want to show. Even if your current date is the 30th of September, the database probably doesn’t contain all the data for that day yet. In many companies, the database has a day delay. So, your safest bet may be to always find the previous month.

This results in below numbers.

You now find a regular Month column (1) in your calendar, and there is a Custom column (2) with the last completely finished month. You now have all the ingredients to duplicate the Month Name column, and replace the last completely finished month with the word default.

= if [Month] = Date.Month( 
                  DateTime.Date( 
                     DateTime.LocalNow( ) 
                  ) 
               ) - 1
     then "Default"
     else [Month Name]

Name your column Month Name Default, and the following is the result:

2.2. Advantages Default Slicer Value

Your newly created column contains values identical to the Month Name column, except that the current month now has the description Default. It is good to notice a few things about this solution:

1. The month September is replaced by the word default, not only for the current year but also for all previous years. This is intended. If you only replace the month name of the current year, this could be problematic.
   Imagine using a slicer with a year number. During your analysis, you want to select the previous year. Unfortunately, with the Default month selected, the previous year is unavailable in the year slicer. After all, the value Default only exists in the current year in your calendar. You would have to take off the Month filter, change the year, and then filter on the month of August. Do the steps in reverse order to get back to the current year.
2. The used solution can be as flexible as you need it to be. If you want to select a period of 3 months ago, that is possible. Also, if you want to select multiple months, you can write code for that too.

2.3. Configure Slicers

With the calendar prepared with your newly created column, we can now incorporate it into the model. Before you add your new column to a slicer, you can go to the Data View, select the column Month Name Default and sort the column by the Month. This ensures the values have the order of the corresponding month number. Your Default value is then always sorted in the position of the month it represents.

When you add Month Name Default to the slicer the dropdown looks as follows:

The slicer no longer shows the name of the last completely finished month. However, for users, this is clear because the sort orders implies what the selected month is. You have now configured your default slicer value to the desired month.

2.4. Create Selected Period Header

If you need more clarity in your report about the month selection, you can add a title to your report showing the selected period. You can base this title on the regular Month Name column, which still contains the month name September instead of default. Something like:

= "Period: "
     & SELECTEDVALUE( Calendar[Month Name] ) 
     & " " 
     & SELECTEDVALUE( Calendar[Year] )

Allowing your report to have both a dynamic slicer selection and a reference to the selected period:

2.5. Create Adjusted Year Column

Last but not least, you can apply a similar technique to the year number. If you want to show the last completely finished month, you also want to show the year of that month. Just make sure that in this new column, the year number is formatted as text. After all, the column is of text type because of the Default value.

You can do that by adjusting the code slightly. Instead of retrieving the previous month’s number, you can retrieve the previous month’s date first. And then transform that data into a year, using the following code:

= if [Year] = 
      Date.Year( 
           Date.AddMonths( 
                DateTime.Date( DateTime.LocalNow() ), 
               -1 )
      )  
     then "Default" 
     else Text.From( [Year] )

Conclusion

And that’s how you can set a default slicer value to the current month or current year in your Power BI report! To make your report even more dynamic, you may enjoy reading how you can support multiple languages in your date table.

Happy querying!