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

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.
How to create power bi date filter
How to create a power bi date filter

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

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

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.
Power bi date filter today
Power bi date filter today
  • Click on the Modelling tab -> New column from the ribbon.
  • Then write the Dax Expression: Today = IF(Sheet1[Order Date]=TODAY(),1,0)
Power bi date filter today
Power bi date filter today
  • 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.
Power bi date filter today
Power bi date filter today

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

Power bi date filter today
Power bi date filter today

Read Power Bi Schedule Refresh

See also  How to set default value in Power Apps data card?

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 )
    )
power bi date filter dax
power bi date filter Dax
  • 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).
power bi date filter dax
power bi date filter Dax

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.
Power bi date filter default to today
Power bi date filter default to today
  • In the power bi slicer, you can click on the dropdown, present in the top right corner.
  • Select relative date from the list.
Power bi date filter default to today
Power bi date filter default to today
  • We will set the slicer for todays date, so in the first box select Last, next to that write 1 and then select days.
Power bi date filter default to today
Power bi date filter default to today

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

Power bi date filter default to today
Power bi date filter default to today

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.
Power bi date filter before today
Power bi date filter before today
  • 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)
Power bi date filter before today
Power bi date filter before today
  • 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.
Power bi date filter before today
Power bi date filter before today

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.

Power bi date filter before today
Power bi date filter before today

Read How to create a filter in Power bi

See also  How to set default value in Power BI Slicer

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"
    )
Power bi date filter current month
Power bi date filter current month
  • 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.
Power bi date filter current month
Power bi date filter current month
  • 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.
Power bi date filter current month
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.
Power bi date filter last 12 month
Power bi date filter last 12 month
  • 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"}})
Power bi date filter last 12 month
Power bi date filter last 12 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.
Power bi date filter last 12 month
Power bi date filter last 12 month
  • 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.
Power bi date filter last 12 month
Power bi date filter last 12 month
  • 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.
Power bi date filter last 12 month
Power bi date filter last 12 month
  • Now when you click on the last 12 month options in the slicer, it will show the result/data of last 12 month.
Power bi date filter last 12 month
Power bi date filter last 12 month
  • When you click on the previous month in the slicer it will show the previous month data.
Power bi date filter last 12 month
Power bi date filter last 12 month

Read Power bi gauge chart – How to use with examples

See also  Power BI Bar Chart Rounded Corners

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.
Power bi date filter slider
Power bi date filter slider
  • In the canvas click on the Power bi slicer, then click on the dropdown arrow and click on between.
Power bi date filter slider
Power bi date filter slider

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

Power bi date filter slider
Power bi date filter slider

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.
Power bi date filter format
Power bi date filter 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
>