Recently, while working on Version 2.0 of Tosilog with Steve N., Steven P., and Noah, we realized that we couldn’t splice the data in Power View by time because each table had its own set of dates. The User Table had its joined_date. The Groups table and Messages table had their own respective created_at date columns. PowerPivot didn’t know that these dates were relative to each other. Luckily there was a solution, create a date table and link it to your other tables! In the second of my Tosilog Blog Series, I’ll be showing you how to do this.
Create a date table in Excel
- In Excel, create a blank worksheet. In cell A1, type a column header name, like DateKey, to identify a range of dates.
- In cell A2, type a beginning date. For example, 1/1/2010.
- Click the fill handle and drag it down to a row number that includes an ending date. For example, 12/31/2016.
- Select all rows in the DateKey column (including the header name in call A1).
- In the Home tab, select Format as Table, and then select a style.
- In the Format as Table dialog box, click OK
- Copy all rows, including the header.
Copy the date table into Power Pivot
- In Power Pivot, on the Home tab, click Paste.
- In Paste Preview > Table Name type a name like Calendar. Leave Use first row as column headers checked, and then click OK.
Linking the Date Table to other Tables in Power Pivot
- Before we can create a relationship between the Date Table and our other tables in the data model, we’ll need to create a Date column of the Date data type to link against.
- Right click on the column next to one of your date columns. For example, in the Messages table, right click on the deleted_at column and select Insert Column.
- Click into the first cell and give the column name. For example, we’ll call this Created Date.
- In the cell below the column name, copy the below DAX Formula. The formula will convert the values in the DateKey column to a date data type and remove the time level precision.
=DATE(LEFT(Messages[created_at],4), MID(Messages[created_at],6,2), MID(Messages[created_at],9,2))
- Now that we have this Date column, we can create the relationship.
- From the Home tab, select Diagram View.
- Select and Drag Created Date from the Messages table to the DateKey in the Calendar table. Repeat the above steps for all of the dates that you have your Power Pivot tables. Below is an image where I’ve linked my tables to my Calendar table.
Now when you combine data from these different sources, you can compare them against time using the data in the Calendar table. In future posts, we’ll cover other DAX formulas and how we can extend our Calendar table with Year, Month, and Quarter information.
I learned (and copied) most of the above information from the following article on the Office Support site: Understand and create date tables in Power Pivot in Excel 2013.