Power bi date filter - Complete tutorial

In this Power bi tutorial, we will see the power bi date filter and also we will discuss the below points:

How to create power bi date filter
How Power bi date filter today
How Power bi date filter dax
How Power bi date filter default to today
How Power bi date filter before today
How Power bi date filter current month
How Power bi date filter last 12 month
How Power bi date filter slider
How Power bi date filter format

Table of Contents

How to create power bi date filter

Here we will see how to create a power bi relative date range filter in power bi desktop. You can do this filter at the page level or report level.

Here I have created a stacked column chart by using order date and sales, and then we will add the order date( contains this year and last year date) to the Power bi page level filter.

And then we will filter the data for the last 30 days including today.

Open power bi desktop. Load the data using get data.
Select stacked column chart from the visualization pane.
In the axis and value field, add order date column and sales from the field pane.

How to create power bi date filter — How to create a power bi date filter

In the filter pane, under filter on this page add order date.
Under filter type select Relative date. Under Show item when the value select is in the last.
Below that write 30, next select days from the dropdown. And then check the box including today.
Click on Apply filter.

In the below screenshot, you can see the bar chart is filtered for the last 30 days including today.

Read Power bi sum group by multiple columns

Power bi date filter today

Here we will see how to filter today’s date from the order date table in the power bi desktop.

I have used the stacked column chart to show the filter result. We will create a column that contains today’s date.

Then we will use that column to filter today’s date from the order date. After applying the filter it will show today’s date and sales in the stacked column chart.

Select the stacked column chart from the visualization pane in power bi desktop.
In the axis and value field, add order date column and sales from the field pane.

Click on the Modelling tab -> New column from the ribbon.
Then write the Dax Expression: Today = IF(Sheet1[Order Date]=TODAY(),1,0)

In the filter pane, under filter on this visual, add today measure.
Under Filter type is Advanced filtering.
Under Show item when the value is 1. Click on Apply filter.

In the below screenshot you can see the power bi date filter today.

Read Power Bi Schedule Refresh

Power bi date filter dax

Here we will see how to filter a table for the date field (within a date range) using Dax. And the output would be a second Power bi table which contains the date range.

Open the Power bi desktop
Load the data using get data.
Here we are using the order table.

power bi date filter dax — power bi date filter Dax

Now we will create table which contains the date range after filtering the original table (date) by using Dax.
For e.g we will filter the date from 05-01-2017(start date) to 15-01-2017(End date), from the orginal table.
For this click on the Modelling tab -> New table.
Then write the DAX expression.

Table = 
VAR DateStart =
    DATE ( "2017", "1", "5" )
VAR DateEnd =
    DATE ( "2017", "1", "15" )
RETURN
    CALCULATETABLE (
        Orders,
        FILTER ( Orders, Orders[Order Date] <= DateEnd && Orders[Order Date] >= DateStart )
    )

In the below screenshot, you can see the power bi date filter using Dax. The output table have the date range from 05-01-2017(start date) to 15-01-2017(End date).

Read Power bi row level security tutorial

Power bi date filter default to today

Here we will how to set today’s date as default in slicer in power bi desktop.

If your sales report has today’s sales data, and you want to use the date slicer to filter today’s sales( which is basically you want to show only 1 day at a time. For this, you can follow the below steps.

In power bi desktop, select the slicer from the visualization.
Then in the field, drag and drop the Order date column from the field pane.

In the power bi slicer, you can click on the dropdown, present in the top right corner.
Select relative date from the list.

We will set the slicer for todays date, so in the first box select Last, next to that write 1 and then select days.

In the below screenshot you can see the power bi filter default today’s date (in table visual) using slicer.

Read Power BI Funnel Chart

Power bi date filter before today

Here we will see the Power Bi date filter before today using slicer in power bi desktop and also want to show if that date having a null value.

In power bi desktop, load the data using get data.
Select the slicer from the visualization pane.
Then in the field pane, drag and drop the order date from the field pane.

Then we will create a column to filter the slicer according to the requirement date.
This column will show 0 on today, -1 of yesterday and so on.
Go to Modelling -> Click on the new column in the power bi ribbon.
Then write the DAX expression

DateOffset = 
DATEDIFF(TODAY(),'Sheet1'[Order Date],DAY)

Now we will use the column as a filter on your date slicer and set it to <=0 or blank.
In the Filter pane, under filter on this visual add date offset (calculated column).
Under filter type, select Advanced filtering, and then under show item when the value is less than or equal to -1.
Select Or, below that select is blank from the list.

Now you can see the yesterday date in the slicer, when you click on the yesterday date, it will show the result of the day before today.

Read How to create a filter in Power bi

Power bi date filter current month

Here we will see the power bi date filter current month using DAX and slicer in power bi desktop.

If you have large sales data and you want to see the current month’s sales data then we will create a measure, which will filter the current month from the date table.

And then you can filter the sales data for the current month by applying the measure in the slicer.

First, we will create a measure which will filter the current month, So for that:

In power bi desktop click on the Modelling -> new column, to create a calculated column that will contain current month.
The measure is

IsCurrentMonth = 
    IF (
        YEAR ( Sheet1[Order Date]) = YEAR ( TODAY () )
            && MONTH(Sheet1[Order Date] ) = MONTH ( TODAY () ),
        "Yes",
        "No"
    )

Select the slicer from the visualization pane in power bi desktop.
In the Field, drag and drop the IsCurrentMonth Calculated column from the field pane.

In the Power bi slicer when you select yes it will show the current month, and when you select no it will show the other month except current month.
In the below screenshot you can see the power bi date filter current month.

Read Power BI waterfall chart

Power bi date filter last 12 month

Here we will see how to show the power bi date filter for the last 12 months using slicer in power bi desktop.

We will create a slicer it will display 3 values like All, previous month, and last 12 months.

In the slicer, if we select last 12 month the slicer will filter and show the last 12 month data.
Similarly if we select previous months from slicer, it will filter and show the previous month data. And when we select all it will show all months data.
For this we will create a measure which will filter according last 12 month, previous months and All.
To use this measure in visual filter, before that we have to create a calculated table( for slicer).
Here i have create a table which contains date of this year(2021) and previous year(2020) and value.

Load the data in power bi desktop using get data.
We will create slicer table (calculated table) by using DAX, click on the Modelling tab -> New table.
Then write the measure:

Slicer = DATATABLE("Value",STRING,{{"All"},{"Last 12 Months"},{"Previous Month"}})

Now we will create measure in your data table which will filter the data when you select the options in slicer, for that click on new measure in the ribbon.
The measure is

Show Contorl = 
var d = SELECTEDVALUE('Table1'[Date])
return
SWITCH(SELECTEDVALUE('Slicer'[Value]),"Previous Month",IF(d >= DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) && d <= DATE(YEAR(TODAY()),MONTH(TODAY()),1),1,-1),"Last 12 Months",IF(d >= DATE(YEAR(TODAY())-1,MONTH(TODAY()),1) && d  <= DATE(YEAR(TODAY()),MONTH(TODAY()),1),1,-1),1)

Now we will create a table visual where we will show the result.

Select the table visual from the visualization pane.
In the value field, drag and drop date and value column from the field pane.

In the filter pane add the show control measure under filter on this visual.
Under show item when the value, select is and next box write 1.
Click on Apply filter.

Now we will create slicer, Click on the slicer from the visualization pane.
In the field, drag and drop the value field from the slicer table in the field pane.

Now when you click on the last 12 month options in the slicer, it will show the result/data of last 12 month.

When you click on the previous month in the slicer it will show the previous month data.

Read Power bi gauge chart – How to use with examples

Power bi date filter slider

Here we will see how to filter data using slicer slider in power bi desktop.

Load the data using get data.
Select the slicer from the visualization pane.
In the field, drag and drop the order date from the field pane.
Make sure that you date column having data type date.
Otherwise change it, by clicking on the date column, it will open the column tool ribbon you can change the data type.
After changing data type it will show date icon next to the date column, if it is showing no need to change.

In the canvas click on the Power bi slicer, then click on the dropdown arrow and click on between.

When you slide the slider it will filter the data accordingly in the visual in power bi desktop.

Read Power BI Date Hierarchy

Power bi date filter format

Here we will see how to change the date format in the filter in the power bi desktop.

When your local date format is mm/dd/yyyy in your table and when you filter the data it shows the format dd/mm/yy. How can change the format of the date column?

In power bi desktop click on the date column in the field pane.
Then column tools ribbon open, next to the format click on the dropdown and you can choose the date format.

You may like the following Power BI tutorial:

In this Power bi tutorial, we learned the power bi date filter. And also we discussed the below points:

Power bi date filter
Power bi date filter today
Power bi date filte dax
Power bi date filter default to today
Power bi date filter before today
Power bi date filter current month
Power bi date filter last 12 month
Power bi date filter slider
Power bi date filter format

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com