When a Date Value Comparison Will Give You Wrong Results in Power Pivot/SSAS Tabular

7th August 2014

I’ve seen a number of new Power Pivot users get confused by unexpected results when comparing date values that appear to be the same. I’ll illustrate this problem with a simple​ example before providing some possible solutions. ​

080714_1502_Whenadateva1

The above Power Pivot table holds the date that each employee started working for a company and the date that they were trained. The ‘Start Date’ and ‘Training Date’ columns have their data type property set to ‘Date’ in Power Pivot.

 

The following DAX formula was used to created the ‘Trained Same Day’ calculated column:

​=

IF(

Employees[Start Date] = Employees[Training Date],

“Yes”,

“No”

)

 

This formula will return a ‘Yes’ for all the rows where the ‘Start Date’ and ‘Training Date’ are the same. When they are not the same, a ‘No’ is returned. We would expect Employee ID’s 1 to 3 to have a ‘Yes’ in the ‘Trained Same Day’ calculated column but this isn’t the case. Take a brief moment to think about why this calculated column is not behaving as expected.

 

The ‘Date’ data type in Power Pivot can be a bit misleading as it actually stores date values with their time parts. When we change the display format of the two date columns to one that shows the date and time, we can see where things are going wrong: The ‘Training Date’ column contains some dates which match the ‘Start Date’ but have a different time value.

 

Note: It’s important to highlight at this point that when dates are loaded into Power Pivot without a time part they will be given a default time value of ’00:00:00′. This is the case with the ‘Start Date’ column.

080714_1502_Whenadateva2

 

The ‘Trained Same Day’ calculated column is actually returning the correct value when we consider the time values that are also held in the columns. We need to re-write the original DAX formula so that it only considers the date part of the ‘Training Date’ column when comparing the values with the ‘Start Date’. There are many ways that this can be done, but one approach is to use a combination of the DATE, YEAR, MONTH, and DAY functions:

​=

IF(

Employees[Start Date]

=

DATE(

YEAR(Employees[Training Date]),

MONTH(Employees[Training Date]),

DAY(Employees[Training Date])

),

“Yes”,

“No”

)

 

Notice how the new calculated column called ‘Trained Same Day New’ gives us the desired behaviour because the date/time values are first stripped of their time part before the comparison is made.

080714_1502_Whenadateva3

 

Alternatively, a calculated column that strips the time part could be added to the table. For example, a calculated column called ‘Training Date New’ could be defined as:

​=

DATE(

YEAR(Employees[Training Date]),

MONTH(Employees[Training Date]),

DAY(Employees[Training Date])

)

 

080714_1502_Whenadateva4

 

After adding this, references to the ‘Training Date’ column in the original DAX formula can be replaced with references to ‘Training Date New’. However, doing this would add to the size of the model.

 

The ideal solution would be to strip the ‘Training Date’ column of a time value before loading it into Power Pivot, but there could be scenarios where this isn’t feasible. The next time you run into a date value mismatch when comparing date values that appear to be the same, remember to check whether the column holds any values for the time part other than the ’00:00:00′ default.

by Michael Amadi at 1:20 in DAX, Power Pivot, SSAS Tabular.