Power BI Slicer between two dates

In this Power BI Tutorial, we will learn about Power BI Slicer between two dates. Also, we will discuss:

• Date range slicer on Power BI
• Relative date slicer on Power BI
• Relative date filter
• Limitation of using relative date range slicer and filter

Date range slicer on Power BI

A slicer visual contains powerful filtering options when with date data types, including graphical date selection, before/after criteria, and relative date such as the last 2 weeks.

By default, Power BI has 2 options, one is List and another is Dropdown. Depending on what data type is used with the slicer, there can be an additional option that comes into play.

Instead of using a list or dropdown slicer, here we are going to using a date range slicer with a horizontal scroll bar.

Step-1:

On the Power BI Report page, create a slicer having a date data field.

Step-2:

Once a date range slicer is added to the page when we click on the down arrow, we can see all of the available options:

• Between– The user given a date range between a start and an end date with a horizontal scroll bar.
• Before– There is an option to pick a date before a specific date or the end date itself with a horizontal scroll bar.
• After– It gives the user an option to pick a date after a specific start date or start date itself with a horizontal scroll bar.
• List– A date must be selcected from a list of given dates using check boxes.
• Dropdown– The user must pick a date from the dropdown list for dates with checxk boxes.
• Relative– Here we can select the ‘Next’, ‘Last’, or ‘This’ date for any count of days, months, or years.

Step-3:

By default, the slicer displays the date with the between option. It is the easiest way because the dates are auto-populated and it’s doesn’t require any manual configuration. According to our data model, both the start date and the end date are showing in a horizontal slicer.

For a better experience, here we are going to create a table chart having the same date column and a product category column.

When we set a date on end date or start date, it will filter the table data like this:

The table chart showing the data, that filtered by the data range slicer (i.e. 1/1/15-1/3/15). This is how a date range slicer works on a Power BI Report.

Read How to create a Power BI Dashboard in Microsoft teams

Relative date range slicer on Power BI Desktop

With a relative date slicer, we can apply a time-based filter to any date column in our data model.

A relative date range slicer is easy to handle and very effective in terms of options that it provides for the user to slice and dice the data.

Let’s have a look to create a relative date range slicer on Power BI with a step by step guide:

Step-1:

Create a slicer having a date data field. select the Relative option from the type of slicer:

Step-2

Then we can select the setting. For the first setting in the relative date range slicer, we have to choose one from the following options such as: last, next, this.

Similarly, the second setting in the relative date range slicer, lets us enter a number to define the relative date range.

The third setting lets us pick the date measurement. We have to choose one from the following choices:

Step-3:

For example, here we can choose last few period, but selecting last, the number of periods we want to have included in our filter, and then selecting the period.

The above slicer is getting the last 5 months from the current month i.e. 3/1/2021-7/28/2021. Considering that today is July 28th of 2021.

This is a simple way to filter your report by last months, last week, last fifth months, etc.

Read Power bi sum group by multiple columns

Relative date filter

We can also create a relative date range filter for our report page or total report. This option is also available for the Filter pane.

To implement this, drag a date field into the page level filters well or the report level filters well in the Filter pane:

After selecting the Relative date option, it will show the same features having 3 sections to change, including a middle numeric field, exact like the slicer:

We can add or remove the today by checked or unchecked the Include today option.

A relative date filtering in the Power BI report is very easy to use, which gives us the option to go back and forth on the selected period range.

Read Power bi measure examples

Limitation of using Relative date range slicer and filter

• The data type that is used in the slicer must be a date, otherwise, it will not show the option in the slicer.
• The data model doesn’t include the time zone information on Power BI.
• The time of the slicer and filter is always based on the UTC time zone.
• We can convert the local time zone data to UTC using the Query Editor.

You may like the following Power BI tutorials:

• Microsoft Power BI KPI Visual
• Power bi slicer contains
• Power bi slicer checkbox
• Power bi slicer multiple columns with examples
• Microsoft Power BI Card – How to use
• Power Bi slicer multiple selections
• How to set default value in Power BI Slicer
• How to create a filter in Power bi
• Power BI average function with Examples

Conclusion

From this Power BI Tutorial, we learned all about the Power BI Date range slicer. Also, we discussed:

• What is Date range slicer and how it works on Power BI?
• How a Relative date slicer works on Power BI?
• What is Relative date filter on Power BI ?
• Limitation of using relative date range slicer and filter