Power BI DAX Date Filter Not Working [3 Main Reasons & Its Solutions]

When working with Power BI, you might encounter issues with date filters that do not work as expected. There can be various reasons for this, such as incorrect DAX formulas, wrong date formats, an improperly set date table, or missing relationships between the date table and data table.

Below, I will present some common problems and their solutions based on my experience working with Power BI DAX date filters.

Date Format Issues in Power BI

While working on Power BI, I was required to create a report that analyzed sales data over time. After loading the data into Power BI, I created a Power BI measure to filter the date column, but it did not give me the exact output I required. The DAX formula was correct when I went to the Power Query Editor. Then I saw that the Date column is a text data type, so we need to change it to a date data type.

Follow the below steps to change the datatype in Power Query Editor:

1. Under the Home tab, click Transform date.

2. Select the date column in the Power Query editor, then click the ABC icon and select Date.

3. Then you can see that our date column data type is Date.

This way, you can change the data format in Power BI using Power Query Editor.

Date Table Missing in Power BI

Sometimes, we do not have a date table while working on Power BI. Follow the steps below to create a date table in Power BI.

Go to the “Modeling” tab, select “New Table,” and use the following DAX formula:

DateTable = CALENDAR(DATE(Year,Month,Day),DATE(Year,Month,Day))

Where:

• DateTable = This is the name of the new table.
• CALENDAR = This function generates a list of continuous dates. Here, we must give starting and ending dates inside the CALENDAR function.
• DATE(Year, Month, Day) = Here, we use the date function to give any specific date.

OR

DateTable = CALENDAR(MIN(YourTable[DateColumn]), MAX(YourTable[DateColumn]))

Where:

• DateTable = This is the name of the new table.
• CALENDAR = This function generates a list of continuous dates between two specified dates.
• MIN(YourTable[DateColumn]) = This calculates the earliest date in the DateColumn of YourTable.
• MAX(YourTable[DateColumn]) = This calculates the latest date in the DateColumn of YourTable.

Relationships Not Set in Power BI

Last week, I worked on a Power BI report where I created a date table but didn’t check the relationship between the two tables. As a result, when creating a calculated column, it showed a #error value. After investigating, I discovered that the relationships between the two tables were not set. Follow the steps below to set up the relationships.

Before setting the relationship, you check that the two tables have the same column or the same column value.

Here, I have a table In Power BI called Extended Sales, in which the date column states 30 April 2024 to 19 June 2024.

At the same time, I have a date table in the same report. The dates range from 1 April 2024 to 1 July 2024.

To create a relationship between two tables, Go to the “Model view,” where you can see two tables.

Then drag “Date” from the “Extended Sales” table and drop it into the “Date” column in the “DateTable.” Here, a New Relationship pop-up will open, and click save.

Then, you see one line connected between two tables, which shows that a relationship has been made.

This way, you resolve the “relationships not set” issue in Power BI.

Lastly, write the correct DAX formula when creating any measure, calculated column, or new table.

I hope this article helped you resolve the Power BI DAX filter issue. If this solution doesn’t work, feel free to ask in the comments box.

Some more Power BI articles you may also like:

• DATEDIFF() in Power BI DAX
• How to Create a Relationship in Power BI Without Unique Values
• How to Filter Date Using Power BI DAX
• How to Filter Distinct Date Using Power BI DAX