Power BI for Beginners: How to create a Date Table in Power BI

So, you’ve learned the basics of Power BI and DAX. Now you’re ready to start applying what you’ve learned into creating some insightful reporting. If you’re a newbie to Power BI like me it can be quite confusing and frustrating on where to start. With the power of the internet there are so many sources to help you learn, but everyone has their own method of how to get things done. I’ve done a bit of the research myself. Having worked with so many different clients over the years I find that the most requested reporting is just a high level KPI report that shows how well a product or campaign is pacing month over month, year over year, month to date, year to date, to goal, etc. For this we will need to understand Time Intelligence and create a custom date table.

Power BI has a built-in feature for Time Intelligence to load auto date/time. It’s a simple and basic feature that should only be used for the bare minimum. When working with large data sets and multiple tables, you should create a date table. Trust me, it will save you a lot of time in the long run.

Let’s start by disabling the auto date/time feature. You’ll want to get into the habit of creating a date table.

1. Click on File and select Options and settings from the menu. Then click on Options. From the Options pop-up you will want to click on ?Data Load from the Current File menu. Under Time Intelligence uncheck the Auto Date/Time box. This will disable the auto date/time feature.
   
2. To create a date table, we’ll want to start by creating a new table and calling it something like Dates.
   
3. Click on the Data view from the left menu. Then select the Modeling tab from the top menu. Click on New Table. Enter the DAX expression below to create a calendar that starts on January 1^st 2017 and ends on December 31^st 2018.
   Date = Calendar( Date(2017, 1, 1), Date(2018,12,31))
4. Press enter and your date table will be created.

Next, we’ll want to create additional columns to based on the date to help us filter on. Month, year, quarter are the most commonly used.

While still in the Data view and Modeling tab you’ll want to click on the New Column button.

1. First, let’s create a column for month with the DAX expression below.
   Month = Format(‘Date'[Date], “MMM yyyy”)
2. Next, we can create quarter.
   Quarter = Year(‘Date'[Date]) & “-Q” & Format(‘Date'[Date], “q”)
3. Last, we can create year.
   Year = Format(‘Date'[Date], “yyyy”)

You can create as many different date formats as needed.

Your Date table should look similar to this.

Now that we have the date columns, we still need to sort the months in the correct order.

1. In the Data view and Modeling tab you’ll want to click on the New Column button.Enter the following DAX expression.
   MonthSort = Format(‘Date'[Date], “yyyy-mm”)
2. Now that the MonthSort column is created, select the Month column and click on Sort by Column from the Modeling tab. Select MonthSort as the column to sort by. This will ensure that your months are sorted correctly in your tables. Otherwise they will be sorted in alphabetical order.
   

Your date table is now completed! But your data isn’t looking right yet. Why is that? It’s because you just have two separate tables created. To make them work together we need to build a relationship between your dataset and the date table.

For our example I have a table called Sales. What we want to do is build a relationship between the Date Table and the Day column within the Sales table.

1. First click on the Relationships icon on the left menu. You will see that both tables do not have any relationships created. Select the Home tab from the top menu and click on Manage Relationships.
   
2. From the Manage Relationships pop-up click on New. From the first drop down, select the Date table and select the Date column.
3. On the next drop down, select the Sales table and select the Day column.
4. From Cardinality drop down select One to many. From the Cross filter direction drop down, select Single. Hit OK and you’re all set.
   

Congratulations! You’ve created a date table and linked it to your dataset. Next time we’ll go over the different time functions in Power BI that we can use to create an insightful KPI report.