Power BI – Careful with DateTime and Date Data Types in Model

This article explains the importance of the Date and DateTime data type change in Power BI. Sometimes, you see column with date as value but it may not be in date data type instead it will be in datetime data type.

Scenario

Table 1 – Date

Description – It contains a date column with date data type.

Table 2 – Sales

Description – It contains ID, OrderDate and Amount fields. OrderDate column in datatime data type.

Loaded the above two tables into Power BI model. As we want to build a report using these two tables, need to establish a relationship between the tables.

Relationship should be built using the ‘Date’[Date] and ‘Sales’[OrderDate] table fields.

Since the data type is different, let me try change the OrderDate field into Date from DateTime data type as like below.

Established the relationship between the Date and Sales tables.

If we build a simple visual using Date from Date table and Amount from Sales table then then we will end up with below result.

Date table contains all the date of Sales but still we are not able to see the Sales Amount by Date.

Problem

This is an issue with data type of OrderDate in Sales table. As you saw above, we already changed the data type from datetime to date but it is not really changed. If you try to see the equal option of the column then you will end up see like below.

The data type of the column is not changed it is still in datetime data type but it just applied the format for datatime to date.

The relationship got established between Date to DateTime columns that is the problem for wrong visual result.

Why the data type not changed?

Tabular engine model support only Date and Date/Time in Data type in DAX as per the Microsoft Document.

Ref – Data types supported in Analysis Services tabular models | Microsoft Docs

Tabular model not able to change the datatime to date data type that is the reason for the problem.

To confirm this again better, let us create DAX table with date field and see how it looks like. If you see DAX table is created and showing the date as datetime data type but actually it is just a date data type.

Solution

You always need to make sure you fix the data type in Power query layer itself. This will be fixed if we change the datetime of OrderDate column into date data type.

Finally, we got the expected result.

Happy Learning!!  Leave your comment below.