Create a relative date slicer or filter in Power BI: 2 Easy Steps

Aditya Jadon • Last Modified: September 4th, 2023

Power BI Date Slicer

Companies widely use Business Intelligence tools for Data Visualization and Data Analysis. It helps them make smarter data drive decisions and accelerate their business growth. These Data Analytics tools such as Power BI, Tableau, Domo, etc allow users to generate reports and extract insights from data.

Power BI is a Business Intelligence platform 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 data. 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.

Table of Contents

What is Power BI?

Power BI Date Slicer : Power BI Logo
Image Source

Power BI is a proprietary Business Intelligence tool designed for seamless Data Analytics and Data Visualization. It is a part of the Microsoft Power Platform. Power BI is one of the common tools used by organizations for analyzing their business data construct reports. 

It comes with a collection of various in-built software services, apps, and connectors that deeply integrates with data sources to deliver immersive visuals, interactive reports, and generate insights. Power BI uses its advanced charts, graphs, and other visuals included with Machine Learning to easily extract valuable information out of data.

Power BI can also read data from XML files, CSV files, JSON format files, and even web pages, then convert raw data into interactive insights. It is available for Desktop, mobile, and on-premise servers. Users can create and share their reports with other Power BI users within the organization or partner companies. 

Key Features of Power BI

Some of the main features of Power BI are listed below:

  • Supports API Integrations: Power BI allows developers to easily integrate with other applications and embed dashboards into other software using sample codes and APIs.
  • Custom Visualization: Power BI offers custom visualization libraries support that allows users to visualize complex data with ease.
  • AI Support: Users can easily perform Data Analytics using Artificial Intelligence. With the built-in AI support, users can prepare data, build Machine Learning models, and gain insights.
  • Modeling View: With the help of Modeling View, Power BI users can slice and divide the complex data into simpler ones that help in better understanding the data, separate diagrams, and multi-select objects.
  • Easy Sharing: Power BI makes it easier for users to easily share their reports within teams, or organizations ensuring full data protection.
  • Hybrid Development: Power BI easily integrates with many 3rd party connectors, applications, and services widely used by organizations that allow users to connect to various data sources.

To know more about Power BI, click here.

Simplify the Power BI Visualization Process with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Power BI, Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 100+ data sources and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

Get Started with Hevo for Free

Its completely automated Data Pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

What is Power BI Date Slicer?

Power BI Date Slicer : Relative Date Range Filter Example
Image Source

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:

  • 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.
  • It helps display the commonly used filters on the report canvas for easier access.

Creating 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.
Choosing Field in Visualizations - Power BI Date Slicer
Image Source
  • 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.
Choosing Relative Date Option in Power BI Date Slicer
Image Source

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
Choosing Reference Option in Power BI Data Slicer settings
Image Source
  • In the next field of Power BI Date Slicer, you can choose a number to define the relative data range.
Defining Relative Date Range - Power BI Date Slicer
Image Source
  • 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)
Selecting Date Measurement - Power BI Date Slicer
Image Source
  • 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.
Power BI Date Slicer - Dragging Date Field to Filter on this Page Field
Image Source
  • 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.
Power BI Date Slicer - Selecting Relative Date option
Image Source
  • 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.
Power BI Date Slicer - Configuring Relative Date Settings
Image Source
  • 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. 

Power BI Date Range Slicer

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

Power BI Date Range Slicer

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.

Conclusion

In this article, you learned about Power BI Date Slicer or Filter and why it helps business users to easily filter through data. 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.

Visit our Website to Explore Hevo

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 Data is a No-code Data Pipeline solution that helps to transfer data from 100+ data sources such as Power BI to desired Data Warehouse. It fully automates the process of transforming and transferring data to a destination without writing a single line of code.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about Power BI Date Slicer or Filter in the comments section below!

No-code Data Pipeline For your Data Warehouse