Date and Date/Time – Sneaky Data Types!

These Two Columns Both Contain Dates, Right? Sure They Do!

Relationships that should work, but aren’t?

Here’s a quick tip, one that I think we’ll all need sooner or later. 

When I got back from last week’s amazing consulting/training trip, the team let me know that we had a problem.  An existing PowerPivot model, one that had been working just fine for a long time, had stopped working when we refreshed the model with the latest data:

All the slicers were now indicating that there wasn’t any data.  And those empty rectangles on the right?  Those are charts.  They just weren’t showing any data anymore.

What’s the problem?

We pretty quickly determined that it had something to do with the date relationships.  When we cleared the date slicer, data came back.  So it was just when we were filtering by Date that data disappeared.

In cases like that, the first thing I like to check is data types.  Somehow, did one of the columns get changed from Date to Text?  Nope, not this time.  They both are definitely still Date, as evidenced by this screenshot from the PowerPivot window ribbon, here at right.

But Data Type Isn’t Enough!

Notice the Format option there, though?  Try setting both related columns to a format that displays time as well as date:

Now look at the two columns:

Like I said, sneaky little Date/Time data type.  When we got new data this time, one of the source systems in the loop decided to include the time of day, whereas before, it had been omitting it.

So yeah, the two columns don’t match up anymore even though by default they LOOKED like they matched.  Sneaky.

So…  if you have a relationship on two Date columns, and the relationship does not seem to be working (all of your measures are returning blanks), this is a likely culprit.

Two ways to fix this

The quickest fix is to create a “clean” calc column that strips the time:

   =DATE(YEAR([CalendarDate]),MONTH([CalendarDate]),DAY([CalendarDate]))

Then you use the calc column for the relationships rather than the original.

But the better, more reliable way to clean your date columns is in the underlying SQL (as long as that’s an option).  If your SQL sources always trim your Date columns down to pure Dates, and truncate Time, then you never have to write calc columns for this purpose again – solve it one place, and save yourself a lot of ongoing work.

Furthermore – columns imported from SQL end up being a lot better compressed by PowerPivot than calc columns, AND this often results in faster pivot performance as well.

If you do the trimming via Views, you can keep the time component in your SQL tables for later, in case you want to do “time of day” analyses.  But your default views that you import should always be protected against this.

And remember, if you aren’t a SQL pro yourself, having a good relationship with the folks who run your databases is a very positive thing.  At Pivotstream for example, I never touch SQL.  But my colleagues who maintain SQL are very helpful, and our cooperation lets us do things that otherwise would be impossible.  One of my favorite themes – cooperation between PowerPivot pros and SQL pros – and I will be hammering said theme every chance I get