How to Filter Date using Power BI DAX

Are you aware of Power BI DAX filter-by-date filtering options? If not, follow this Power BI tutorial to filter the values based on the date selection or between two dates. Furthermore, we will also cover a few related topics, as mentioned below.

Power BI DAX filter by date
Power BI DAX filter between two dates
Power BI DAX filter max date
Power BI DAX filter date range
Power BI DAX filter date greater than
Power BI DAX filter date less than
Power BI DAX filter datediff
Power BI DAX filter-specific date
Power BI DAX filter last 30 days
Power BI DAX filter previous 7 days
Power BI Dax filter relative date
Power BI DAX filter latest date or Power BI Dax filter last date
Power bi Dax calculates filter date
Power bi Dax date filter not working
Power BI Dax filter last year
Power bi Dax filter by date distinct
Power bi Dax ignores date filter
Power BI DAX filter year to date
Power BI Dax filter current month
Power BI Dax filter current year
Power BI Dax filter last month
Power bi Dax filter by date of the previous month

Power BI DAX filter by date

Let us see how we can apply a filter by date in the Power Bi desktop using Power bi DAX.

In this complete post, I have used the below-mentioned Employee table data. You can download it from here. You can see there are two date columns presented in it.

Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:

filter by date = 
VAR JoinDate = DATE ( "2019", "7", "19" )
var ret = if(Employees[Date of Joining].[Date] == JoinDate, True,False) 
return ret

Where,

Filter by date = New Column Name
JoinDate = Variable Names
Employees = Existing Table Name
Date of Joining = Column Name

In the below screenshot, you can see that the filter by date column displays the data based on the applied condition as highlighted.

This is how to apply a filter by date and check the values in the Power Bi desktop using Power Bi DAX.

Also, read: How to merge columns in Power Query

Power BI DAX filter between two dates

Let us see how we can apply a filter between two dates using DAX in the Power Bi desktop.

I have used the Employee table data in this example to apply a filter between two dates.

Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Table from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar and select the check icon:

Two dates = 
VAR JoinDate = DATE ( "2019", "7", "19" )
VAR DateEnd = DATE ( "2022", "7", "20" )
    
RETURN                                                                                                                                                                                                                                                                                                                                              CALCULATETABLE (
 Employees,
         DATESBETWEEN ( Employees[Date of Joining],JoinDate,DateEnd))

Where,

Two dates = Table Name
JoinDate & DateEnd = Variable Names
Employees = Existing Table Name
DatesBetween = Function Name
Date of Joining = Column Name

The screenshot below shows that the table data has been filtered and displayed for the mentioned two between dates.

Power BI DAX filter between two dates example

This is how to apply a filter between two dates on the Power Bi desktop.

Check: How to Merge Column in Power BI

Power BI DAX filter max date

Let us see how we can apply the filter by checking the max date value using Power BI DAX in Power BI.

In this example, I have used the Employee table data. You can see two date columns presented in it so that we can check the max date value for the date columns.

Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:

checkdate = 
Var d2 = max(Employees[Date of Joining].[Date])
var ret = if(Employees[Last Date].[Date] <= d2, 0,1)

return ret

Where,

checkdate = Table Name
d2 & ret = Variable Names
Employees = Existing Table Name
Date of Joining, Last Date = Column Names

The screenshot below shows that the table data has been filtered and displayed based on the max date value:

This is how to apply the filter by checking the max date value using Power BI DAX in Power BI.

Read: Power BI Slicer Multiple Columns

Power BI DAX filter date range

Let us see how to apply the filter between the date ranges using Power BI DAX in Power BI.

In this example, I have used the Employee table data. Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Table from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar and select the check icon:

Range = 
VAR DateStart = DATE ( "2019", "2", "27" )
VAR DateEnd = DATE ( "2022", "7", "20" )
RETURN
    CALCULATETABLE (
        Employees,
        FILTER ( Employees, Employees[Date of Joining].[Date] <= DateEnd && Employees[Date of Joining].[Date] >= DateStart )
    )

Where,

Range = Table Name
DateStart & DateEnd = Variable Names
Employees = Existing Table Name
Date of Joining = Column Names

The screenshot below shows that the table data has been filtered and displayed for the mentioned or the dates-selected ranges.

This is how to apply the filter between the date ranges using Power BI DAX in Power BI.

Also Checkout: Power BI Create Table From Another Table

Power BI DAX filter date greater than

Let us see how to apply the filter date values greater than the selected values using Power BI DAX in Power BI.

In this example, I have used the Employee table data. Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Table from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:

Filter by Date Greater than = 
Var Maximum = max(Employees[Date of Joining].[Date])
var ret = if(Employees[Last Date].[Date] >= d2, 0,1)

return ret

Where,

Filter by Date Greater than = Column Name
Maximum & ret = Variable Names
Employees = Existing Table Name
Date of Joining & Last Date = Column Names

The screenshot below shows that the table data has been filtered and displayed for the mentioned or the dates-selected ranges.

This is how to apply the filter date values greater than the selected values using Power BI DAX in Power BI.

Read: Power BI Compares Two Columns in Different Tables

Power BI DAX filter date less than

Let us see how to apply the filter date values less than the selected values using Power BI DAX in Power BI.

In this example, I have used the Employee table data. Open the Power BI desktop and load the data into it.
Click on the Table Tools tab -> New Table from the ribbon. Then write the below-mentioned Dax Expression in the formula bar and Select the check icon:

Filter by Date Less than= 
Var d2 = max(Employees[Date of Joining].[Date])
var ret = if(Employees[Last Date].[Date] <= d2, 0,1)

return ret

Where,

Range = Table Name
DateStart & DateEnd = Variable Names
Employees = Existing Table Name
Date of Joining = Column Names

The screenshot below shows that the table data has been filtered and displayed based on the applied conditions.

This is how to apply the filter date less than the selected date value using Power BI DAX in Power BI.

Check: Power BI Sum Group by

Power BI DAX filter datediff

Let us see how we can apply the filter using the DateDiff function measure in Power BI.

In this example, I have used the Employee table data. Initially open the Power BI desktop and load the data into it.
Click on the Table Tools tab -> New Column from the ribbon. Then write the below-mentioned Dax Expression in the formula bar:

Days Count = IF([Status]="InActive",DATEDIFF(Employees[Date of Joining].[Date],Employees[Last Date].[Date],DAY))

Where,

Days Count = Column Names
DATEDIFF = Function Name
Employees = Existing Table Name
Date of Joining, Status, Last Date = Column Names

The screenshot below shows that the table data has been filtered and displayed based on the applied conditions.

This is how to apply the filter using the DateDiff function measure in Power BI.

Power BI DAX filter-specific date

Let us see how we can apply the filter of the data tables only to the specific dates or the selected dates using the Power Bi Dax formula in Power BI.

In this example, I have used the Employee table data. Initially open the Power BI desktop and load the data into it.
Click on the Table Tools tab -> New Table from the ribbon. Then write the below-mentioned Dax Expression in the formula bar:

Filtered Dates = 
CALCULATETABLE(
    Employees,
    DATESBETWEEN ( Employees[Date of Joining], DATE(2019,8,27), DATE(2019,9,6) )
)

Where,

Filtered Dates = Table Name
DATES BETWEEN = Function Name
Employees = Existing Table Name
Date of Joining= Column Names

The screenshot below shows that the table data has been filtered and displayed based on the applied conditions.

This is how to apply the filter of the data tables only for the specific dates or the selected dates using the Power Bi Dax formula in Power BI.

Checkout: Power BI Report Export to PDF [With 20+ Examples]

Power BI DAX filter last 30 days

Let us see how we can filter the table data for the last 30 days using Power Bi DAX in Power BI.

Open the Power BI desktop and load the data into it. Click on the Table Tools tab -> New Column from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar:

Last 30 Days = 
CALCULATE (
    SUM (Employees[Days Count]),
    DATESINPERIOD ( Employees[Date of Joining].[Date], MAX ( Employees[Date of Joining] ), -30, DAY )
)

Where,

Last 30 Days = Table Name
DATES BETWEEN = Function Name
Employees = Existing Table Name
Date of Joining, Days Count= Column Names

In this example the below screenshot, filters and displays for the last 30 days from the current date (ie: Today’s date: 20/09/2022), it displays the data accordingly as shown below:

This is how to filter the table data for the last 30 days using Power Bi DAX in Power BI.

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

Power BI DAX filter previous 7 days

Let us see how we can filter the table data for the Previous 7 days using Power Bi DAX in Power BI,

Initially open the Power BI desktop and load the data into it. Click on the Table Tools tab -> New Column from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar:

Previous 7 Days = 
CALCULATE (
    SUM (Employees[Days Count]),
    DATESINPERIOD ( Employees[Date of Joining].[Date], MAX ( Employees[Date of Joining] ),-7,YEAR )
)

Where,

Previous 7 Days = Table Name
Employees = Existing Table Name
Date of Joining, Days Count= Column Names

In the below screenshot, it filters and displays for the Previous 7 days from the current date (ie: Today’s date: 20/09/2022) and displays the data accordingly as shown below:

This is how to filter the table data for the previous 7 days using Power Bi DAX in Power BI.

Power BI Dax filter relative date

To work with the Power BI Dax filter relative date, you can refer to this complete Power BI Tutorial: Power BI Date Slicer [With 41 real examples]

Power BI DAX filter latest date Or Power BI Dax filter last date

Let us see how we can filter the latest date using the Power BI DAX filter function in Power BI.

In this example, I will show the latest date based on the status column, we can filter the date for both the Active and Inactive employees.

Initially open the Power BI desktop and load the data into it. Click on the Table Tools tab -> New Column from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar: In the example, I have filtered the data based on the status inactive.

Latest Date = 
VAR LastSuccessDate =CALCULATE (
    MAX ( 'Employees'[Date of Joining].[Date] ),FILTER ( 'Employees', [Status] = "InActive" ))
return LastSuccessDate

Where,

Latest Date = Column Name
LastSuccessDate = Variable Name
Employees = Existing Table Name
Date of Joining, Status= Column Names

In the below screenshot, it filters and displays the latest date of status InActive column,

In the same way, we can check the latest date of Status Active Mode as shown below, to write the below-mentioned DAX formula

Latest Date = 
VAR LastSuccessDate =CALCULATE (
    MAX ( 'Employees'[Date of Joining].[Date] ),FILTER ( 'Employees', [Status] = "Active" ))return LastSuccessDate

Where,

Latest Date = Column Name
LastSuccessDate = Variable Name
Employees = Existing Table Name
Date of Joining, Status= Column Names

This is how to filter the latest date using the Power BI DAX filter function in Power BI.

Power bi Dax calculates filter date

Let us see how we can calculate the filter date using the Power BI DAX filter function in Power BI.

In this example, I will show you how to calculate the min date based on the status column, we can use the calculate filter the date for both the Active and Inactive employees.

Open the Power BI desktop and load the data into it. Click on the Table Tools tab -> New Column from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar: In the example, I have filtered the data based on the status inactive.

Calculate Minimum Date = 
VAR  MinimumDate =CALCULATE (
    MIN ( 'Employees'[Last Date].[Date] ),FILTER ( 'Employees', [Status] = "InActive" ))
return MinimumDate

Where,

Calculate Minimum Date = Column Name
MinimumDate = Variable Name
Employees = Existing Table Name
Last Date, Status= Column Names

In the below screenshot, you can see that it calculates the minimum date value and filters and displays it in the employee’s table data as highlighted below:

This is how to use filter dates using the Power BI DAX filter function in Power BI.

Power bi Dax date filter not working

When you use Power Bi Dax to filter the date value, make sure you are following the correct Date Format.
If the Power BI Dax date filter is not working, then try changing the Date format of the Date column to “YYYY-MM-DD” on the Power BI desktop.

Power BI Dax filter last year

Let us see how we can calculate and filter the last year’s data using the Power BI DAX filter function in Power BI.

In this example, I have used the financials sales data, opened the Power BI desktop and load the data into it using the get data option.
Click on the Table Tools tab -> New Measure from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar:

Sales last year = CALCULATE(SUM(financials[ Sales]),SAMEPERIODLASTYEAR((financials[Date].[Date])))

Where,

Sales last year = Measure Name
financials = Existing Table Name
Sales, Date= Column Names

Select the Table visual from the visualization, drag and drop the created measure in the
In the below screenshot, you can see that it calculates and filters the last year’s data using the Power BI DAX filter function.

This is how to calculate and filter the last year’s data using the Power BI DAX filter function in Power BI.

Power bi Dax filter by date distinct

Let us see how we can calculate and filter the data using the distinct date function in Power BI DAX.

In this example, I have used the financials sales data, opened the Power BI desktop, and load the data into it using the get data option.
Click on the Table Tools tab -> New Measure from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar:

Sales Count = calculate(DISTINCTCOUNT(financials[ Sales]), ALLSELECTED(financials[Date 1].[Date]), filter(financials, NOT(isblank(financials[ Sales]))))

Where,

Sales Count = Measure Name
financials = Existing Table Name
Sales, Date= Column Names

Select the Table visual from the visualization, drag and drop the created measure in the
In the below screenshot, you can see that it displays the sales count values based on the Product using the Power BI DAX filter function and Distinct function.

This is how to calculate and filter the data using the distinct date function in Power BI DAX.

Power bi Dax ignores date filter

Let us see how we can calculate and filter the data using the date filter without ignoring it in Power BI DAX.

In this example, I have used the financials sales data, opened the Power BI desktop and load the data into it using the get data option.
Click on the Table Tools tab -> New Measure from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar:

Filter = CALCULATE(FIRSTDATE('financials'[Date 1].[Date]),
                            ALL('financials'[Date 1].[Date]),
                            FILTER(
                                ALL(financials),
                                financials[Country] = MAX(financials[Country])
                            ))

Where,

Filter = Measure Name
financials = Existing Table Name
Country, Date= Column Names

Select the Table visual from the visualization, drag and drop the created measure in the
In the below screenshot, you can see that it displays the max date value based on the Country using the Power BI DAX filter function.

power bi Dax ignores date filter — power bi Dax ignores the date filter

This is how to calculate and filter the data using the date filter without ignoring it in Power BI DAX.

Power BI DAX filter year to date

Let us see how we can use the filter year-to-date option using the Power BI DAX filter function in Power BI.

In this example, I have used the financials sales data and loaded the data into the Power BI desktop using the get data option.
Click on the Table Tools tab -> New Measure from the ribbon.
Then write the below-mentioned Dax Expression in the formula bar:

sales YTD = CALCULATE(financials[Sales Count],DATESYTD(financials[Date 1].[Date]))

Where,

sales YTD = Measure Name
financials = Existing Table Name
Sales Count= Column Names

Select the Table visual from the visualization, drag and drop the created measure in the
In the below screenshot, you can see that it displays year-to-date based on the Product using the Power BI DAX filter function.

This is how to use the filter year-to-date option using the Power BI DAX filter function in Power BI.

Power BI Dax filter current year

Let us see how we can get the current year’s sales data using Power BI Measure in Power BI.

In this example, I am going to use the financial data to calculate the current year’s sales value using Power BI DAX.
Load the data to the Power BI desktop using the get data option. Then create a new measure and apply the below formula to find the current year’s sales value.

Current Year Sales = 
VAR CurrentYear = YEAR(TODAY())
RETURN 
CALCULATE(financials[Sales Count],financials[Year] = CurrentYear )

Where,

current year = Variable name
financials = Existing Table Name
Sales Count,Year = Column names

Once the Values are calculated, select the Table visual and drag and drop the calculated Measure. It will display the current year’s value.
The screenshot below shows that the Table visual displays the current Year’s (2022) Total sales value.

This is how to get the current Year’s sales data using Power BI Measure in the Power BI.

Power BI Dax filter current month

To filter the current month using Power BI Dax, check out this complete tutorial: Get Current Month Sales Report using Power BI Measure

Power bi Dax filter by date of the previous month

Let us see how we can filter by date of the Previous Month using the Power BI DAX function in Power BI.

In this example, I am going to use the below-mentioned Employee sample data to calculate the Previous month’s employees’ salary amount value using the Power BI DAX function.

Load the data to the Power BI desktop using the get data option. Then create a new measure and apply the below formula to find the Previous month’s employees’ salary amount value.

Previous Month = 
var currentmonth = MONTH(MAX(Employees[Date of Joining]))
var PM =IF(currentmonth=TODAY(),currentmonth,currentmonth-1) 
return CALCULATE(SUM(Employees[Emp Salary]),MONTH(Employees[Date of Joining])= PM)

Where,

current month ,PM = Variable names
Employees= Existing Table Name
Date of Joining = Column names

Once the Values are calculated, select the Card visual and drag and drop the calculated Measure. It will display the previous Months value.
In the below screenshot, you can see that card visual displays the Previous Month’s Value, where the current Month is (September 2022).

Power bi Dax filter by date of the previous month

This is how to filter by date of the Previous Month using the Power BI DAX function in Power BI.

Also, you may like the below Power BI Tutorials:

How to Filter Power BI Dax Based On Condition
How to Append Columns in Power Query
Power BI Matrix Multiple Column
How to remove rows in power query editor [With various examples]
Power BI filter between two dates [With 15+ Examples]
Power BI Sum Multiple columns [With 21 Useful Examples]
Power BI divides two columns [With 14 real examples]

This Power BI Tutorial explained how to filter the data by date using the Power BI DAX formula in Power BI. Also, we covered all these below-related topics:

Power BI DAX filter by date
Power BI DAX filter between two dates
Power BI DAX filter max date
Power BI DAX filter range
Power BI DAX filter date greater than
Power BI DAX filter date less than
Power BI DAX filter datediff
Power BI DAX filter-specific date
Power BI DAX filter last 30 days
Power BI DAX filter previous 7 days
Power BI DAX filter latest date
Power bi Dax calculates filter date
Power bi Dax date filter not working
Power BI Dax filter last year
Power bi Dax filter by date distinct
Power bi Dax ignores date filter
Power BI DAX filter year to date
Power BI Dax filter current month
Power BI Dax filter current year
Power BI Dax filter last month
Power bi Dax filter by date of the previous month

Bijay Kumar

I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com