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
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:
- Move a date field into one of the filters (all pages, page or visual filter)
- Select the Filter type drop-down
- Select Relative date.
- After selecting a relative date, 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:
- 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.
- 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.
- 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.
- 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.
- 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 achieve that for a regular slicer? The answer is by adjusting the calendar values 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 day of each month to make it fit in a single picture.
2.1. Create Adjusted Month Column
To satisfy our requirements, we will create a new column called Month Name Default to our Date Table. Instead of mentioning the regular month names, this column will show the text ‘Current’ for the current month, and ‘Previous’ for last month.
To do that, we can use the following expression:
let
CurrentMonth = Date.Month( DateTime.LocalNow() ),
DefaultMonthColumn =
if [Month] = CurrentMonth then "Current" else
if [Month] = CurrentMonth-1 then "Previous" else [Month Name]
in
DefaultMonthColumn
Adding this to a column called ‘Month Name Default’ then gives us:
This column contains the regular Month Names, the only difference being that the current and previous month values are shown as ‘Current‘ and ‘Previous‘. And with each passing of the months, this value moves along.
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:
- 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. - 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 selection looks as follows:
The slicer no longer shows the names of the current and previous month. However, for users, this is clear enough 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 names July and June. 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 "Current"
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!
Date Table Series
Foundations
Creating a (Dynamic) Date Table
Build a Calendar that supports Multiple Languages
Creating a 445 Calendar (incl 454 and 544)
Advanced Topics
Create ISO Week and Year Column
Return Nth Business Day From Date
Compute Working Days Between Dates
Creating Ordinal Numbers (1st, 2nd, 3rd etc.)
Create Default Slicer Value of Current Month
Is it just me or does anyone else find this rather clunky – it essentially reuquires users to be told why the normally intuitive date slicer is missing a month name and what “default” represents. Sure, it achieves the intended aim, but UX-wise it’s pretty bad.
Hi Rick,
Thanks for this video this was very helpfull for me!
I have one question:
How can I get this to work if I want to use the month name of the current month instead of “Default” ? Any idea?
Thanks again
Grtz Tim
Hey Time. Unfortunately that’s not possible at the moment. Microsoft is working on alternative ways to achieve such, but we’ll have to wait for that to release in the future. Cheers
Thanks for taking the time to create this – helped me out quite a bit on what I was trying to do!
Great video Rick….
how can I build on this and use this to create a “current month” and a “prior month” label?
Current Month being the most recent closed Accounting period (i.e. if we are currently in February 2023, the “Current Month” should show Jan 2023.
Prior Month being the month prior to the Current Month. (i.e. Dec 2022)
My goal is then to be able to isolate the current and prior months, and create a month on month variance – critical in business!
Cheers 🙂
GREAAAT ! VERY VERY USEFUL ! THANK YOU ! SUPER !
One approach is to use PowerQuery with a List.Max function – in this example combined with another filter:
super vids
when it is the month of January it does not work correctly
Guess you could use the:
Date.AddMonths( [Date], -1 ) function. Then grab the month from this shifted date 🙂
Awesome post Rick! And also the video is crystal clear!
Thank you!! Appreciate the support ^^
Rick