Power BI filter between two dates [With 15+ Examples]

In this Power BI Tutorial, we will learn Power BI Filter between two dates with different examples. And also we will discuss the below points:

  1. Power BI filter between two dates Dax
  2. How to filter between two dates in Power BI
  3. Power BI slicer between filter between two dates
  4. Power BI calculates the filter between two dates
  5. Power BI filter between two relative dates
  6. Power BI filter between two date columns
  7. Power Bi date slicer between a default to today
  8. Power BI filter date before today
  9. Power BI one date filter for multiple tables
  10. Power BI dynamic date filter
  11. Power BI filter between two dates excluding weekends
  12. Power Bi filter between two dates find duplicates
  13. Power Bi filter between two dates null
  14. Power Bi filter between two dates of the month
  15. Power BI filter between two dates quarters

Power BI filter between two dates Dax

  • The filter is used to filter the data based on the condition applied in Power BI, where we have to choose the column field value in the value section, and the result occurs based on the applied filter.
  • In the following, we will see how to filter the data between two dates using the Power BI measure, In this example, I have used this sample excel sheet, you can also download it from here.

How to filter between two dates in Power BI

Let us see how to filter the data between two dates in the Power Bi using the Filter fields pane,

In this example, I have used the below sample Table data (Projects) to filter between two dates in Power BI. We can easily apply filters in the filters pane and display the data in the visuals.

How to filter between two dates in Power BI
How to filter between two dates in Power BI
  • Load the data using the get data option on the Power BI desktop, then select the table visually and add the column fields that you want to display in the table visual as shown below:
How to filter between two dates in the Power BI
How to filter between two dates in the Power BI
  • Now to expand the Filter pane and select the data field that you want to apply a filter, here I have selected the StartDate data field.
  • Choose the Filter type as Advanced filtering, and apply the condition that you want to filter and show.
  • Here I have applied a filter between two dates to filter the data as shown below: Once the condition is used click on the Apply filter option.
filter between two dates in the Power BI example
Power BI filter multiple dates
  • In the below screenshot, you can see it filters and displays the data in the table visual based on the condition applied.
filter between two dates in Power BI example
filter between two dates in the Power BI example

This is how to filter the data between two dates in the Power Bi using the Filter fields pane.

Also, read: Power BI Sum Multiple columns

Power BI slicer between filter between two dates

Let us see how we can apply a filter between two dates using a single date slicer in Power BI,

Here, I have used the same Project table data to filter the two dates using a single date slicer in Power BI.

  • Select the table visual and add the fields that you want to display in the table visual as below. Here I have selected the Team field, Assigned Projects, and Completed Projects.
Filtering on multiple dates using a single date slicer
Filtering on multiple dates using a single date slicer
  • Select the Slicer visual and add the date column fields to it, so that you can filter the table data based on the filter applied between two dates.
Filter Multiple Date Columns in One Slicer
Filter Multiple Date Columns in One Slicer
  • In the below screenshot, you can see that I have selected a date range from 2nd June to 4th in the date slicer, it filters and displays the data for the applied filter between two columns.
Filtering on multiple dates using a single date slicer example
Filtering on multiple dates using a single date slicer example

This is how to apply a filter between two dates using a single date slicer in Power BI.

Check out: Power BI divides two columns [With Examples]

Power BI calculates the filter between two dates

Let us see how we can calculate the filter between two dates in Power BI,

In this example, we will use two tables one is the sales table and the other one is the Duration table as highlighted below:

Sales Table:

Power BI calculates the filter between two dates
Power BI calculates the filter between two dates

Duration Table:

Power BI calculates the filter between two dates example
Power BI calculates the filter between two dates example
  • We will calculate the Sales value( Sales table) between the Sales started date and the Sales ended date. The result would look like this:
  1. 27/June/2022 to 30/June/2022= 1605
  2. 01/July/2022 to 05/July/2022 = 1720
  • Once the data is Loaded to the Power BI desktop, create a measure to calculate the values between two dates.
  • Click on the New measure, and apply the below-mentioned formula:
Total Sales = 
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        Sales,
        Sales[Date] >= MIN( 'Duration'[Sales Started Date] )
            && Sales[Date] <= MAX ( 'Duration'[Sales Ended Date] )
    )
)
power bi filter between two dates using DAX
power bi filter between two dates using DAX
  • Then create a table visual, drag and drop the Sales Started Date column and Sales Ended date column, and Total Measure in the value field.
power bi filter between two dates using measure
power bi filter between two dates using a measure

This is how to calculate the filter between two dates in Power BI.

Also, check: Stacked Bar Chart in Power BI [With 27 Real Examples]

Power BI filter between two relative dates

Let us see how we can apply filters between dates using the relative filter type in Power BI,

Here, I have used the same Project table data to filter the two dates using the relative filter type in Power BI.

  • Select the table visually and add the column fields that you want to display in the table visual as shown below:
Example of Power BI filter between two relative dates
Example of Power BI filter between two relative dates
  • Now to expand the Filter pane and select the data field that you want to apply a filter, here I have selected the StartDate data field.
  • Choose the Filter type as Relative date filtering, and apply the condition that you want to filter and show.
  • In the Relative date type filter, we have three options to display the value, is in the last, is in this, or is in the next.
Power BI filter between two relative dates example
Power BI filter between two relative dates example
  • Here I have applied a filter to display the values for the last 48 days Once the condition is used click on the Apply filter option.
Power BI filter between two relative dates
Power BI filter between two relative dates
  • In the below screenshot, you can see it filters and displays the data in the table visual based on the applied condition.
Example of Power BI filter between two relative date
Example of Power BI filter between two relative date

This is how to apply filters between dates using the relative filter type in Power BI.

Read: Power BI Bookmarks [With 21 Examples]

Power BI filter between two date columns

Let us see how we can apply a filter between two date columns in Power BI and filter the rows based on the selected date ranges in Power BI,

Here, I have used two tables with the same Project table data to filter the two date columns and another one is the Calculated date table in Power BI.

Using the date slicer we can filter the two date columns, if the data falls in between the Start Date column and End Date column then it filters and displays the data accordingly.

  • To create a data table, Choose Modeling then select the New Table option from the ribbon on the Power Bi desktop as shown below:
Power BI filter between two date columns
Power BI filter between two date columns
  • Then apply the formula below to create a data table using the calendar function, and also you can set the start date range and end date range in Power BI.
Date Table = CALENDAR (DATE (2022, 6, 1), DATE (2022, 7, 31))
  • Once the data table is created, Select the slicer visual, and in the values section drag and drop the date column field.
Power BI filter between two date columns example
Power BI filter between two date columns example
  • Select the table visually and add the column fields that you want to display in the table visual as shown below:
Example of Power BI filter between two date columns
Example of Power BI filter between two date columns
  • Click on the New measure, and apply the below-mentioned formula:
Range = 
var StartRange=FIRSTDATE('Date Table'[Date])
var EndRange=LASTDATE('Date Table'[Date])
return 
if(
    SELECTEDVALUE(Projects[Start Date])>= StartRange
    &&
    SELECTEDVALUE(Projects[End Date])<=EndRange
    &&
    SELECTEDVALUE(Projects[End Date])<>BLANK(),
1,0)

where,

  1. Range = Measure Name
  2. StartRange , EndRange = Variables Name
  3. Date Table, Projects = Table Name
  4. Start Date, End Date = Two Date columns
Example of the Power BI filter between two date columns
Example of the Power BI filter between two date columns
  • Then select the table visual, expand the filters pane and add the range measurement to the data field and apply the condition, once the condition is applied click on the Apply filter option.
Example of the Power BI filter between two date column
Example of the Power BI filter between two date column
  • In the below screenshot, you can see it displays the raw data based on the date range. When you slice the slicer if the data falls in between the selected range values then it displays only that row of data.
Example of Power BI filter between two date column
Power BI filter rows between two dates

This is how to apply a filter between two date columns in Power BI and also filter the rows based on the selected date ranges in Power BI.

Read: Clustered Column Chart in Power BI [With 45 Real Examples]

Power Bi date slicer between a default to today

Let us see how we can set the Power BI data slicer between a default to today,

  • Yes, when we use the between the format for the Date slicer. We can set the end date of the date range to Today’s date.
  • To create a data table, Choose Modeling then select the New Table option from the ribbon on the Power Bi desktop as shown below:
Power Bi date slicer between default to today
Power Bi date slicer between a default to today
  • Then apply the formula below to create a data table using the calendar function, and also you can set the start date range and end date range in Power BI.
Date Table = CALENDAR (DATE (2022, 6, 1), DATE (2022, 7, 31))
  • Once the data table is created, Select the slicer visual, and in the values section drag and drop the date column field.
Power Bi date slicer between default to today date
Power Bi date slicer between a default to today’s date
  • To set the end date as today’s date then write the below measure, and the end date will be automatically changed to today’s date.
Date Table = CALENDAR (DATE (2022, 6, 1),TODAY())
  • In this case, Today’s date is (20-7-2022). So in the below screenshot, you can see that the end date range value is set as today’s date.
Power Bi date slicer between default to today example
Power Bi date slicer between a default to today’s example

This is how to set the Power BI data slicer between default to today.

Also, check: Power bi change color based on value [With 13 real examples]

Power BI filter date before today

Let us see how we can filter the Sales data of the date before today in Power BI,

In this example, I have used the below sample Table data (Sales) to filter dates before today. So here we can see the Sales that occurred before today.

Power BI filter date before today
Power BI filter date before today
  • Make sure the data has been loaded to the Power BI desktop, then select the table visually and add the column fields that you want to display in the table visual as shown below:
Power BI filter date before today example
Power BI filter date before today’s example
  • Click on the New measure, and apply the below-mentioned formula:
TotalSales = SUM(Sales[Date])
CHKdate = If([TotalSales]<=TODAY(),0,1)

Where,

  • total sales, CHKdate =Measure Names
  • Sales = Table Name
  • Date = Column name
Example of Power BI filter date before today
Example of Power BI filter date before today
  • Then select the table visual, expand the filters pane and add the CHKdate measure to the data field and apply the condition, once the condition is applied click on the Apply filter option.
  • Here I have applied a filter to display the sales values that occurred before today. And today’s date is (20-07-2022). Once the condition is applied click on the Apply filter option.
  • In the below screenshot, you can see it filters and displays the data in the table visual based on the applied condition.
Example of the Power BI filter date before today
Example of the Power BI filter date before today

This is how to filter the Sales data of the date before today in Power BI.

Read: Power BI calculated column [With 71 Useful Examples]

Power BI one date filter for multiple tables

Let us see how we can apply the filter using one date filter with multiple tables in Power BI,

  • In this example, I have used two tables Project Table and Sales Table. When I slide the date Slicer it should filter the data and display them based on the condition.

Project Table:

Power BI one date filter for multiple tables
Power BI one date filter for multiple tables

Sales Table:

Power BI one date filter for multiple table
Power BI one date filter for multiple tables
  • Make sure data has been loaded into the Power Bi desktop, Create a calculated date table by applying the below measure:
Date Table = CALENDAR(DATE(2022, 6, 1),DATE (2022, 7, 31))
  • And Select the Slicer under the visualizations and add the Date field into it as shown below:
Power BI one date filter for multiple table example
Power BI one date filter for multiple table example
  • Select the table visually and add the column fields that you want to display in the table visual as shown below: For Sales Table, I have added Sales and Date column fields. For Project Table, I have selected the Team and StartDate column fields:
Power BI one date filter for the multiple tables
Power BI one date filter for the multiple tables
  • Now make the relationship between the two tables with the calculated date column, so that when you slide the date slider, the filter will apply to both the tables.
Example of the Power BI one date filter for multiple tables
Example of the Power BI one date filter for multiple tables
  • In the below screenshot, you can see that using the one date filter we can filter multiple tables in Power BI. Here I have applied a filter to display data for the mentioned dates (01-06-2022 to 03-06-2022).
Power BI using one date filter for multiple tables
Power BI using one date filter for multiple tables

This is how to apply a filter using one date filter with multiple tables in Power BI.

Read: Power BI sync slicers [With 15 useful examples]

Power BI dynamic date filter

Let us see how we can filter the data based on the dynamic data filter using DAX in Power BI,

  • Here, I have used the same Project table data to filter the two dates using a single date slicer in Power BI.
  • Select the table visual and add the fields that you want to display in the table visual as below. Here I have selected the Team and Assigned Projects field.
Power BI dynamic date filter
Power BI dynamic date filter
  • Select the Slicer visual and add the End date column fields to it, so that you can filter the table data based on the filter applied between two dates.
  • Here we will, if the assigned task is completed or not completed by the Selected Team.
Power BI dynamic date filter example
Power BI dynamic date filter example
  • Click on the New measure, and apply the below-mentioned formula, Once the measure is created drag and drop the column field into the Table visual.
Task Status = IF(SELECTEDVALUE(Projects[End Date])<= TODAY(),"Completed","Notcompleted")
Power BI date range column
Power BI date range column
  • In the below screenshot, you can see that I have selected an End date range in the date slicer, it filters and displays the data for the applied filter between two columns.
Power BI filter rows between two dates
Power BI filter rows between two dates

This is how we can filter the data based on the dynamic data filter using DAX in Power BI.

Read: Power BI Add Calculated Column [With Various Examples]

Power BI filter between two dates excluding weekends

Let us see how we can filter the values using calculate function between two dates and excluding the weekend’s value count in Power BI.

  • Here in this example, I am going to use the below-mentioned Sales table data along with the date column.
Power BI filter between two dates excluding weekends
Power BI filter between two dates excluding weekends
  • Here, we will calculate the sales amount value excluding the weekends, In this example, I have the Date column from ( 1 st June 2022 to 15th June 2022).
  • Make sure the data has been loaded, now create a new measure, and calculate the Sum of total sales values.
TotalSales = CALCULATE(SUM(Sales[Sales]),DATESBETWEEN(Sales[Date],"1/6/2022","15/6/2022"))
  • Select the card visually, and in the values field drag and drop the created measure. In the same way, select the table visual, and drag the column fields that you want to display in it.
Power BI filter between two dates excluding weekends example
Power BI filter between two dates excluding weekends example
  • To exclude the weekends, initially, we have to create a calculated column to find the weekdays.
  • Here the weekday is calculated in this format ( Monday will be considered as week day 2 and Sunday will be considered as week day 7).
  • Select the new column from the ribbon, and apply the below measure.
Weekday = WEEKDAY(Sales[Date],2)
Microsoft Power BI filter between two dates excluding weekends example
Microsoft Power BI filters between two dates excluding weekends example
  • Now to exclude the weekends, select the TotalSales and apply the below formula,
TotalSales = CALCULATE(SUM(Sales[Sales]),DATESBETWEEN(Sales[Date],"1/6/2022","15/6/2022"),Sales[Weekday]<6)
  • In the below screenshot, you can see that it excludes the weekend’s sale value count and displays the remaining count in the card visual.
Microsoft Power BI filter between two dates excluding weekends
Microsoft Power BI filters between two dates excluding weekends

This is how to filter the values using calculate function between two dates and excluding the weekend’s value count in Power BI.

Check out this post: Line Chart in Power BI [Complete Tutorial with 57 Examples]

Power Bi filter between two dates find duplicates

Let us see how we can filter and find the duplicates between the two date ranges in Power BI,

  • Here in this example, I am going to use the below-mentioned Projects table data along with the date column.
Power Bi filter between two dates find duplicates
Power Bi filter between two dates find duplicates
  • Here, we will find the duplicate values between two date ranges, Make sure the data has been loaded, now create a new Calculated column and apply the following formula:
Column = IF(CALCULATE(COUNT(Project[Start Date]),ALLEXCEPT(Project,Project[Start Date])) >1,"Yes","No")
Power Bi filter between two dates find duplicates example
Power Bi filter between two dates find duplicates example
  • In the below screenshot, you can see that the duplicate values display the data that has the same date range then it shows Yes else No.
  • Here I have used the table visual to display the data in Power BI.
Microsoft Power Bi filter between two dates find duplicates example
Microsoft Power Bi filters between two dates to find duplicates example

This is how to filter and find the duplicates between the two date ranges in Power BI.

Power Bi filter between two dates null

Let us see how we can apply a filter between two dates if the date column has a null value or blank value in Power BI.

In this example, I have used the below-mentioned sample project data, where the Start Date and End date have null values in them.

Filter between two dates with nulls and blanks
Filter between two dates with nulls and blanks
  • Load the data into the Power Bi desktop, create two separate Tables by selecting the New Table option for the Start Date and End date and apply the below-mentioned formula :
StartDate = CALENDAR(DATE(2022,6,1),DATE(2022,6,10))
EndDate = CALENDAR(DATE(2022,6,11),DATE(2022,6,20))
  • where StartDate and EndDate = Table Names
  • Now we will show if the date column has a Null value or Blank value then it displays 1 else 0. For that create a New Measure and apply the below-mentioned formula:
Measure = 
var minstartdate = MIN(StartDate[Date])
var maxstartdate = MAX(StartDate[Date])
var minenddate = MIN(EndDate[Date])
var maxenddate = MAX(EndDate[Date])
return
if((ISBLANK(MAX(Project[Start Date]))||ISBLANK(MAX(Project[End Date]))||                                                                                  ((MAX(Project[Start Date])>=minstartdate && MAX(Project[Start Date])<=maxstartdate) &&                                                                 MAX(Project[End Date])>=minenddate&&MAX(Project[End Date])<=maxenddate)),1,0)
Power Bi filter between two dates null
Power Bi filter between two dates null
  • Check the measure, select the table visual, and drag and drop the column fields that you want to display in the table.
  • In the below screenshot, you can see that the date column which has a null or blank value displayed as 1, and the not blank value results as 0.
Power Bi filter between two dates null example
Power Bi filter between two dates null example

This is how to apply a filter between two dates if the date column has a null value or blank value in Power BI.

Read: Power BI Group By [With 51 real examples]

Power Bi filter between two dates of the month

Let us see how we can filter between two dates of the month in Power Bi,

Here I have used the below sample data to filter the two dates of the month in Power BI, In this, the Sales started from ( 01-06-2022) to (07-09-2022)

Power Bi filter between two dates of months
Power Bi filter between two dates of months
  • Load the data into the Power Bi desktop, Select the table visually and add the Sales and date hierarchy field to it.
Microsoft Power Bi filter between two dates of months
Microsoft Power Bi filter between two dates of months
  • Select the Slicer visual and add the date column fields to it and change the slicer to relative date, so that you can filter the table data based on the filter applied between two dates.
Power Bi filter between two dates of month example
Power Bi filter between two dates of month example
  • Select the Months option from the Relative Date slicer, and apply the filter according to your requirement.
  • Here I have selected the Next 2 Months, where the current month is July and for the next two months August and September it filters the Sales data and displayed them in the table visual.
  • In the below screenshot, you can see for the selected two dates of the month filters and displays the sales value.
Microsoft Power Bi filter between two dates of month example
Microsoft Power Bi filter between two dates of month example

This is how we can filter between two dates of the month in Power Bi.

Power BI filter between two dates quarters

Let us see how we can filter between two dates of Quarters in Power Bi,

  • Here also I have used the same sales sample data, which I have used for the previous heading to filter the two dates of quarters in Power BI.

Before that, we have to know about the Quarters.

  1. First quarter -> Q1: 1 January – 31 March
  2. Second quarter -> Q2: 1 April – 30 June
  3. Third quarter -> Q3: 1 July – 30 September
  4. Fourth quarter – > Q4: 1 October – 31 December
  • Load the data into the Power Bi desktop, Select the table visually and add the Sales and date hierarchy field to it.
  • Select the Slicer visual and add the date column fields to it and change the slicer to relative date, so that you can filter the table data based on the filter applied between two dates.
Power BI filter between two dates quarters
Power BI filter between two dates quarters
  • Here I have selected the last 1 Month, where the current month is July and for the previous month June it filters the Sales data and displayed them in the table visual.
  • In the below screenshot, you can see for the selected two dates of the month it filters and displays the sales value and displays the Quarter value.
Power BI filter between two dates quarters example
Power BI filter between two dates quarters example

This is how to filter between two dates of Quarters in Power BI.

Also, you may like some more Power BI Tutorials:

In this Power BI Tutorial, we have learned how to apply filters between two dates with some examples and also covered the below-mentioned topics:

  • Power BI filter between two dates Dax
  • How to filter between two dates in Power BI
  • Power BI slicer between filter between two dates
  • Power BI calculates the filter between two dates
  • Power BI filter between two relative dates
  • Power BI filter between two date columns
  • Power Bi date slicer between a default to today
  • Power BI filter date before today
  • Power BI one date filter for multiple tables
  • Power BI filter between two dates excluding weekends
  • Power Bi filter between two dates find duplicates
  • Power Bi filter between two dates null
  • Power Bi filter between two dates of the month
  • Power BI filter between two dates quarters

  • Good day – thanks for this, I see you MVP in sharepoint, really hoping you could assist me – Going to create a list input on sharepoint – user will input a ID then start & end date and a value. In Power BI – I need to extract the data into a table showing each day (date) and the value divided by the number of days between the start and end dates.

  • >