DateKey: How to relate your date data

If the idea of relationships in Power BI is completely new to you, you may wish to review some of my previous posts, such as Part II: It's Complicated Relationships in Power BI.

To give a quick recap, in order to create successful relationships between two tables, we should always check a few things: 

• Does the data type match? 
• Are they in the same format? 
• Is there a one to one or one to many cardinality? 

In this post, we will look more closely at what we mean by 'same format' and use the most common example of where we get this wrong - dates. 

Assuming both your date table and fact table use Date/Time data type, you may find your data is in the following format: 

FACT Table

DimDate Table

Although both sets of data are in the format M/D/YYYY hh:mm:ss AM/PM, what difference do you notice? 

Look more closely at the DimDate table times - each row has 12:00:00 AM for the time. This is true of any TRUE date table - we are only allowed a single row per date, so we must have only ONE time for each date. Usually that time is 12:00:00 AM. 

How often does your data have exactly 12:00:00 AM as its time? In this example, how often is the submit date at exactly 12:00:00 AM? Almost never. 

So if we look at our rules again, let's see where the problem lies: 

• Does the data type match?  Yes - both are Date/Time.

• Are they in the same format?  Yes - both are M/D/YYYY hh:mm:ss AM/PM

• Is there a one to one or one to many cardinality? Yes - DimDate has unique values for Date.

So what's the problem? Our M/D/YYYY hh:mm:ss AM/PM format does not allow for any matching data. So we really need to add another rule to our list: 

• Is there a matching record in the Dim table for every record in the Fact table?

In this rule we fail. 4/22/2019 4:58:56 PM will NEVER appear in the DimDate table - it only shows 12:00:00 AM. So, we need to create a DateKey column that will follow all of our three initial rules AND provide matching records for each row of data. 

Creating DateKey

I like to use YYYYMMDD for my DateKey, and I add a new column in Power Query for every date that will need to be used in a relationship in Power BI.

Open Power Query Editor. Click the Add Column tab > Custom Column. 

If your data has DateTime column that you want to get DateKey of, use this formula: 

=DateTime.ToText([Date], "yyyyMMdd")

If your data has Date column that you want to get DateKey of, use this formula: 

=Date.ToText([Date], "yyyyMMdd")

Click OK to create your column. 

Repeat for ALL Date fields in your data that will be used in relationships.

Close & Apply or Close & Load your changes. 

Now you should be able to create relationships in Power BI or Excel with greater success.