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.

• 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:

• 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.

• In the below screenshot, you can see it filters and displays the data in the table visual based on the condition applied.

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.

• 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.

• 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.

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:

Duration Table:

• 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] )
    )
)

• 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.

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:

• 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.

• 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.

• In the below screenshot, you can see it filters and displays the data in the table visual based on the applied condition.

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:

• 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.

• Select the table visually and add the column fields that you want to display in the table visual as shown below:

• 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

• 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.

• 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.

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:

• 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.

• 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.

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.

• 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:

• 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

• 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.

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:

Sales Table:

• 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:

• 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:

• 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.

• 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).

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.

• 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.

• 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")

• 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.

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.

• 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.

• 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)

• 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.

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.

• 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")

• 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.

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.

• 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)

• 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.

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)

• 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.

• 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.

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.

• 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.

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

Also, you may like some more Power BI Tutorials:

• Power bi conditional column [With 17 Useful Examples]
• Power BI date hierarchy [With 21 real examples]
• How to create a report in Power BI Desktop? [Excel & SharePoint]
• Power BI Date Slicer [With 41 real examples]
• Power BI split column [With 13 real examples]
• Power BI DAX Min Date Validation

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