How to Create a Relative Date Slicer or Filter in Power BI?

Power BI is owned by Microsoft, allowing companies to visualize and make better use of their business data. Companies have years of data stored in multiple data sources, and it becomes difficult to navigate from date ranges to filter data. Power BI Date Range Filter and Slicer provide users with features to easily select and visualize historical data with preferred date ranges.

Power BI Date Slicers and Filters can be added to any column with date data, and users can easily navigate through data to get a better understanding of it. In this article, you will learn about the steps to how to use Power BI Date Filter and Slicer and some points to remember while using these Power BI Date Slicer and Filters.

What is Power BI Date Slicer?

Power BI Date Slicer and Filter allow users to apply a time-based filter to any columns in your data. There are many Power BI Date Slicers, and each one has a different purpose. It helps users filter flexibly select between date ranges to show only data of a particular range. After refreshing the data, the relative period will automatically get applied for the relative date constraint. 

When to Use Power BI Date Slicer?

Power BI Date Slicers are easy to use and provide flexibility to users in filtering data based on dates. Users should use Slicer for the following reasons listed below:

• It helps display the commonly used filters on the report canvas for easier access.
• So that users can view the currently filtered state without opening a drop-down list.
• It allows users to create focused reports by applying Power BI Date Slicers and Filters next to the important visuals.
• Users can filter the unneeded and hidden data tables.

How to Create a Power BI Date Slicer?

Now that you have understood about Power BI Date Slicer. In this section, you will learn how to create and use Power BI Date Slicer and Filters. You can use Power BI Date Slicer just like any other Power BI Slicer. The following steps to create Power BI Date Slicer are listed below.

Step 1: Create a Slicer

• Open your Power BI and load the dataset.
• Now from the Vusializations tool section, select the “Slicer” option to create a report with Power BI Data Slicer.
• Then from the “Field” value, choose the column to which you want to add Power BI Date Slicer. 
• For this tutorial, “Purchasing Date” is used for Power BI Date Slicer.

• Now, click on the Power BI Date Slicer from your canvas and then click on the upper-right of the Slicer visual. 
• If the column has data then it will show the “Relative Date” in the drop-down menu.

Step 2: Setting Relative Date

• Here, select the “Relative Date” option to add Power BI Date Slicer to the column.
• Then, select the “Settings” option.

• In the first field of the Power BI Data Slicer settings, you can choose the following option listed below.
  • Last
  • Next
  • This

• In the next field of Power BI Date Slicer, you can choose a number to define the relative data range.

• In the third set, you can select the date measurement. It has the following option listed below.
  • Days
  • Weeks
  • Weeks (Calendar)
  • Months
  • Months (Calendar)
  • Years
  • Years (Calendar)

• For example, if you select the “Months” option from the last field of Power BI Date Slicer settings and 2 in the middle field.
• So, if today is 14 March, then the visual data constrained by the Slicer will show you the data of the previous two months.
• The data will start from 15 January to 14 March. But if you select the “Months (Calender)” option on the last field of Power BI Date Slicer, then it will show data from 1 May to June 30, without taking the date into consideration.

Creating Power BI Date Filter

In this section, you will go through the steps to create a relative data range filter to add to your report pages or your entire report. The following steps to add Power BI Date Filter are listed below. 

Step 1: Create a Date Range Filter

• Open your report in Power BI and from the “Fields” pane, select the “Date” field.
• Now, drag it to the “Filter on this page” field or “Filters on all pages” from the “Filters” option.

• You can also change the relative data range options, similarly, you can customize in Power BI Date Slicer. 

Step 2: Configuring Date Range Filter

• For this, select the “Relative date” option from the “Filter-Type” drop-down menu.

• After selecting the “Relative Date” option. It will provide you with the 3 options under the “Show items when the value” section for configuring Power BI Date Filter.

• These are similar options to what you configured while creating Power BI Data Slicer. 
• Now, here in the middle field of Power BI Date Filter, you can choose a number to define the relative data range.
• Next, click on the “Apply filter” option.

That’s it! You have successfully created Power BI Date Slicer and Filter.

How to Create a Power BI Date Range Slicer?

Slicer visuals include powerful filtering options for date data types, such as a graphical date picker, criteria before and after, and relative dates such as the last two weeks. 

 By default, Power BI has two options, one is a list and the other is a dropdown. Additional options may work, depending on the type of data used by the slicer. 

 Instead of using a list or dropdown slicer, we’ll use a date range slicer with a horizontal scroll bar here.

Step 1:  On the Power BI report page, create a slicer with a date data field. Once the date range slicer is added to the canvas,  you can click the down arrow (shown in the image below) to see all the available options. 

Step 2:  After adding the date range slicer to the page, click the down arrow to see all available options.

Understand how to use each of the available options one by one. 

• Between -The user is given a date range between the start and end dates in the horizontal scroll bar. 
• Before -You have the option to use the horizontal scroll bar to select a date before a specific date or the end date itself. 
• After – Use the horizontal scroll bar to give the user the option to select a date after a specific start date or the start date itself. 
• List – You must use the checkbox to select a  date from a list of predefined dates. 
• Dropdown – The user must select a date from the date dropdown list with checkboxes. 
• Relative – Here you can select the Next, Last, or This date in any number of days, months, or years.

Step 3: By default, the slicer displays the date with the Between option, so you can change it as per your requirement. Further investigation has shown that this is also the easiest option, as the data is automatically populated and does not require manual configuration. A horizontal scroll bar displays the start and end dates, allowing you to select a range between the two dates.

Points to Consider When Using Power BI Date Slicer

Some of the limitations and considerations of using Power BI Date Slicer and Filter are listed below:

• The data type of the field that you will use for the Slicer should be a date, not the default of text to ensure that the relative options will show up in the slicer.
• The data models in the Power BI don’t have information related to time zones. So, data can be stored in Power BI but without any indication of the time zone.
• The Power BI Date Slicer works on the time in UTC. So if you are sharing your reports with others in different time zone then they will view the data in your time zone. 
• The Query Editor allows users to convert the data stored in the local time zone to the UTC.

Best Practices to Create Effective Dashboards

• Let users know when exactly they are filtering by using clear labeling for your date slicers.
• Use a default date range to help users get started easily.
• Don’t clutter your dashboard with many filters or date slicers.
• The date slicers should be consistent across your dashboard so that it’s easy to navigate.
• Select the correct date slicer type for your data which includes a basic slicer, a relative date slicer, and a date range slicer. This is important to enhance the user experience.

Let’s also understand some advanced tips for using PowerBI below:

• Use custom DAX formulas to create complex data filters such as “last year to date.”
• It’s possible to create more than one date slicer on a single visualization by filtering each by a different date field.
• It’s possible to create dynamic date slicers that automatically adjust when the visualization data changes.
• Filter your data based on a relative time period using the feature “relative date filtering.” This will be beneficial for tracking trends over a period of time.
• Utilize the “drillthrough” feature in Power BI to create detailed date slicers. This helps you identify patterns or issues in your data by drilling down into specific time periods.

Before wrapping up, you can give it a read the basics if you are interested.

Conclusion

In this article, you learned about Power BI Date Slicer or Filter and why it helps business users filter through data easily. Also, you read how to create Power BI Date Slicer or Filter. Relative date range filters make the reports flexible and allow easy access to data. Power BI is a widely used BI tool among companies.

Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (60+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Sign up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Frequently Asked Questions