Power BI is a highly flexible tool that lets users filter data based on date ranges in multiple ways. We’ll explain how to add a date range filter to your reports, create a dynamic date filter using the filter pane, and why you should use slicers to filter reports instead of filtering directly in a dataset using Power Query.
Slicers are a core visual component of Power BI. While Power BI has a Filtering function, adding a slicer visual to your report places the capability front and center where report viewers can easily see it without having to put any thought into it.
Date slicers can be formatted into multiple styles, adjusted for date ranges, minimum dates or for specific time periods. We’ll explain how to add and configure date slicers to your report.
Let’s jump in!
Table of Contents
Power BI Slicers vs Filters
Power BI provides users with two options to filter their data based on dates. A Filter Panel that sits to the side of a report that lists all of potential fields they can filter a report on, or Slicers. Slicers work similar to visuals and developers can drag and drop them into the middle of a dashboard for highly visible, highly formattable date selection options.
Both are great options for giving report viewers the ability to drill into and explore reports on demand, but slicers give developers a greater level of control over how users interact with reports. Slicers have the added benefit of being placed in the middle of a report where users who are new to Power BI can easily see the filter. When using the Filter panel, it can be confusing or many novice Power BI users are unaware that it exists.
Pre-Requisites to Setting up a Date Slicer
Before you add a date slicer to your report, you should Check Column Data Types. There are times that incoming datasets have dates in odd formats, or columns have not been defined as a Date data type. The type of data that you add to a slicer visual will determine the slicer options available. Power BI automatically adjusts slicer options based on whether a column is text, numeric, or a date.
If you’re not sure about the data type of a column, look at the Data panel on the right side of Power BI. Dates will have a calendar icon next to them, text will be blank, numbers will have a sum or sigma sign next to them.
If the date field does not have a calendar next to it, you can update it in the Data View or by updating it in Power Query.
How to Add Date Range Slicers to Power BI
To add a date range slicer to filter a dashboard, you will need to add a slicer to your report, assign a date field to it and setup other visualizations with the same date field or make sure that a relationship exists between multiple tables, if applicable. Date slicers can be setup with multiple formats to make sure that the interaction method meets the needs of the Power BI developer.
To add a date range slicer, follow these steps:
Step 1.) Add a slicer visual to your report. A slicer works similar to other visuals and is in the Visualizations panel of. Power BI.
Step 2.) Assign a date field to the slicer. When you first add a slicer it will show as an empty box. Drag and drop a field from the Data Panel to the Field selection when the slicer is selected.
Slicers can be moved around to different parts of your dashboard or report. If a relationship exists between different tables, or you’re working with a single table use the same date field on a different visualization. When you adjust the date range filter the rest of the visuals on the page will adjust accordingly.
The screenshot above is the default date slicer setup that lets you select a date range with a slider. There are additional settings that are also good to be aware of when adding a date slicer to your report that will give you even more control.
Types of Power BI Date Range Slicers
Power BI lets developers go a step beyond creating date range slicers and gives them the ability to pick specific dates, present buttons for dates, use relative date ranges, and even choose a before or after date range to dynamically filter a report.
To change the type of Date range slicer in Power BI, click on the slicer after adding it to your report to select it. Then navigate to the Visualizations panel, and click the Format Visual button represented by a chart with a paint brush. Expand the options menu and you can see the different styles available.
Power BI Dates
- Vertical List – A list box where you can check each date you want to see.
- Tile – A series of buttons with each individual date specified on it.
- Between – The default date range filter
- Before – Similar to the between slicer but only has one slider to adjust the end date
- After – Similar to the between slicer but only has one slider to adjust the start date
- Dropdown – A dropdown list to select individual dates
- Relative Date – Choose to see the last 7 days, 30 days, 90 days, etc.
- Relative Time – Similar to date but based on hours, minutes etc.
The following example shows a date slicer setup as a tile. It presents each individual date as a button that can be clicked.
This specific setup isn’t very useful for individual dates, but can be adjusted to only show months, years or other groupings you have setup in a date hierarchy.
Using Date Hierarchies with Slicers
Date Hierarchies allow users to drill down from year to quarter, month, and day or you can control which grouping they’re able to see. For example, only giving users the ability to filter by month can be useful when working with accounting datasets. Many financial statements are incomplete mid-month and would be missing month end adjusting entries.
By limiting the date periods a user can filter on, you can curate the report and avoid misleading information being presented.
To use part of a date hierarchy in a slicer, drag and drop the specific date grouping to the slicer field. In the example below we have a tile formatted date slicer with months assigned giving people the option to choose a month by name.
Another method for curating the date ranges that people can select is by adding multiple date fields into the slicer field. This creates a hierarchy view that report viewers can use to select a full quarter or one month at a time.
Try experimenting with different combinations of field values and slicer formats. The options are almost endless.
If you’re unfamiliar with Power BI Date Hierarchies, check out our guide on setting them up.
How to Use Date Filters in Power BI
Power BI Developers can assign a date filter to a report if they want to allow report viewers to filter date ranges using the filter panel, or to permanently set a specific date range or relative date range filter on a dashboard. To apply a report filter, make sure the Filters panel is turned on under the View tab, expand it and you can drag and drop a date field into the Filters on This Page section.
Pages, visuals, and entire reports can be filtered depending on the section they are added to. Filters can be set with specific date ranges, or relative date ranges..
Options at the top right of the filter box allow you to toggle the visibility of the filter for report viewers and to toggle whether or not they can edit them further using the eye and padlock icons.
The filter pane can be a nice alternative to adding slicers on to a page to avoid the report getting too cluttered but can also be difficult for users new to Power BI to know that it’s there.
To learn even more about filters, slicers and choosing the right method for your report, the guys at Guy in a Cube explain some more options that are worth checking out!
Filtering Dates in Power Query
Another way to filter data between date ranges is doing so in Power Query, the data modeling side of Power BI. It can be an attractive option because it limits the amount of data being pulled into your report from the very beginning. It can help reports run faster but limits the number of dynamic options you have when filtering. It’s a more permanent solution but can be worth exploring if you want to filter a date range of all data that people will ever be able to interact with.
Conclusion
Date range slicers are a visually appealing way to give your end users the ability to limit or expand the view of data that they see when interacting with a report. Slicers have multiple formatting options, and styles giving the developer more control over how report viewers can interact with the report.
Going a step beyond styles, you can also change fonts, colors, and slicer sizes to blend in with your other visuals for an even more immersive reporting experience.
If you want to avoid slicers all together, you can also filter Power BI reports using the filter panel which can be equally powerful but is less widely used and understood by report viewers. For more permanent filtering, you can also filter date ranges in Power Query which limits the data that comes into the report view in the first place. It can be an appealing option to speed up reports and keep people from going too far back into the past.