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.

  1. Power BI DAX filter by date
  2. Power BI DAX filter between two dates
  3. Power BI DAX filter max date
  4. Power BI DAX filter date range
  5. Power BI DAX filter date greater than
  6. Power BI DAX filter date less than
  7. Power BI DAX filter datediff
  8. Power BI DAX filter-specific date
  9. Power BI DAX filter last 30 days
  10. Power BI DAX filter previous 7 days
  11. Power BI Dax filter relative date
  12. Power BI DAX filter latest date or Power BI Dax filter last date
  13. Power bi Dax calculates filter date
  14. Power bi Dax date filter not working
  15. Power BI Dax filter last year
  16. Power bi Dax filter by date distinct
  17. Power bi Dax ignores date filter
  18. Power BI DAX filter year to date
  19. Power BI Dax filter current month
  20. Power BI Dax filter current year
  21. Power BI Dax filter last month
  22. 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.

Power BI DAX filter between two dates
Power BI DAX filter between two dates
  • 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,

  1. Filter by date = New Column Name
  2. JoinDate = Variable Names
  3. Employees = Existing Table Name
  4. 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.
Power BI DAX filter by date
Power BI DAX filter by date

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,

  1. Two dates = Table Name
  2. JoinDate & DateEnd = Variable Names
  3. Employees = Existing Table Name
  4. DatesBetween = Function Name
  5. 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
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,

  1. checkdate = Table Name
  2. d2 & ret = Variable Names
  3. Employees = Existing Table Name
  4. 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:
Power BI DAX filter max date example
Power BI DAX filter max date example

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,

  1. Range = Table Name
  2. DateStart & DateEnd = Variable Names
  3. Employees = Existing Table Name
  4. 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.
Power BI DAX filter range
Power BI DAX filter range

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

See also  Power BI DAX Min Date

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,

  1. Filter by Date Greater than = Column Name
  2. Maximum & ret = Variable Names
  3. Employees = Existing Table Name
  4. 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.
Power BI DAX filter date greater than
Power BI DAX filter date greater than

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,

  1. Range = Table Name
  2. DateStart & DateEnd = Variable Names
  3. Employees = Existing Table Name
  4. Date of Joining = Column Names
  • The screenshot below shows that the table data has been filtered and displayed based on the applied conditions.
Power BI DAX filter date less than
Power BI DAX filter date less than

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,

  1. Days Count = Column Names
  2. DATEDIFF = Function Name
  3. Employees = Existing Table Name
  4. 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.
Power BI DAX filter datediff
Power BI DAX filter datediff

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,

  1. Filtered Dates = Table Name
  2. DATES BETWEEN = Function Name
  3. Employees = Existing Table Name
  4. Date of Joining= Column Names
  • The screenshot below shows that the table data has been filtered and displayed based on the applied conditions.
Power BI DAX filter-specific date
Power BI DAX filter-specific date

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,

  1. Last 30 Days = Table Name
  2. DATES BETWEEN = Function Name
  3. Employees = Existing Table Name
  4. 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:
Power BI DAX filter last 30 days
Power BI DAX filter last 30 days

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

See also  How to Change yyyymmdd to Date Format 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,

  1. Previous 7 Days = Table Name
  2. Employees = Existing Table Name
  3. 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:
Power BI DAX filter previous 7 days
Power BI DAX filter previous 7 days

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,

  1. Latest Date = Column Name
  2. LastSuccessDate = Variable Name
  3. Employees = Existing Table Name
  4. Date of Joining, Status= Column Names
  • In the below screenshot, it filters and displays the latest date of status InActive column,
Power BI DAX filter latest date
Power BI DAX filter latest date
  • 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,

  1. Latest Date = Column Name
  2. LastSuccessDate = Variable Name
  3. Employees = Existing Table Name
  4. Date of Joining, Status= Column Names
Power BI Dax filter last date
Power BI Dax filter last date

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,

  1. Calculate Minimum Date = Column Name
  2. MinimumDate  = Variable Name
  3. Employees = Existing Table Name
  4. 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:
Power bi Dax calculates filter date
Power bi Dax calculates filter date

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,

  1. Sales last year = Measure Name
  2. financials = Existing Table Name
  3. 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.
Power BI Dax filter last year
Power BI Dax filter last year

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,

  1. Sales Count = Measure Name
  2. financials = Existing Table Name
  3. 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.
Power bi Dax filter by date distinct
Power bi Dax filter by date distinct

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

See also  Power BI If Statement | If Contains Power BI

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,

  1. Filter  = Measure Name
  2. financials = Existing Table Name
  3. 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,

  1. sales YTD = Measure Name
  2. financials = Existing Table Name
  3. 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.
Power BI DAX filter year to date
Power BI DAX filter year to date

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,

  1. current year = Variable name
  2. financials = Existing Table Name
  3. 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.
Power BI Dax filter current year
Power BI Dax filter current year

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.
Power BI Dax filter last month
Power BI Dax filter last month
  • 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,

  1. current month ,PM = Variable names
  2. Employees= Existing Table Name
  3. 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
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:

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
>