How to Create a Date Table in Power BI — Fast and Easy

Behind the scenes, Power BI automatically analyzes and defines the columns from your dataset — dates, texts, numbers, etc. — and create hierarchies and models.

If you’re using Power BI Service, you won’t be able to see them. But Power BI Desktop has a data view and a model view where you can peek inside your dataset.

For example, in the Retail Analysis Sample, you can see the different tables from the dataset using Power BI Desktop:

Like what you see from the image above, it was Power BI who connected all the dots.

However, some data analysts and experts like to make their own tables or edit a table’s contents.

Though you can create the date table in any view, switching to the data view would make it easier for you to track any changes to your table.

To change the view, click the table icon on the left side pane:

To add a new date table, go to the ‘Modeling’ tab and click ‘New Table’ from the ribbon:

Now that you have a new table, you would want to name it and use the ‘CALENDAR’ function to define the date range you would like your table to have.

To make the purpose of this table clear, let’s name it “Date Table”:

As you can see, after writing ‘CALENDAR’, Power BI will give you a hint on how to finish the expression similar to how Excel does it.

For example, the retail analysis sample already has a date range in a table labeled “Time”.

Upon close examination, you can see that the column labeled “Month” contains data about the day, month, and year.

To define the table, all you have to do is reference the table and column of your existing data.

Here’s how:

Date Table = CALENDAR(FIRSTDATE(Date formatted column),LASTDATE(Date formatted column))

With the Retail Analysis Sample, the expression becomes:

Date Table = CALENDAR(FIRSTDATE('Time'[Month]),LASTDATE('Time'[Month]))

Now you’ve got a date table based on your existing dataset:

Congratulations! You just made your first date table in Power BI! 🎉

The problem is:

What if you have no data source?

No problem! All you have to do is create your own table and use DAX functions to define it.

Instead of giving reference to your dataset, just define your start date and end date.

Let’s try putting in “jan 1 2013” as the start date and “dec 31 2013” as the end date:

Date Table = CALENDAR("jan 1 2013","dec 31 2013")

A date table for the whole year of 2013 will appear:

Pretty easy, right? 😁

If you created a new date table, chances are, you would like to put up columns to house the information and categories of your date range for your needs.

There are DAX functions you can use to add columns. However, Power BI made it convenient by adding a ‘New Column’ button.

Name your new column and define the date type in your column.

Let’s say you would to only house the year in this column. You will have to use the ‘YEAR’ function and refer the column of your newly created date table.

The expression will then become:

Year = YEAR('Date Table'[Date])

This time, let’s try adding a month column and format it to show the month name and not the month number.

The expression will then become:

Month = FORMAT('Date Table'[Date],"MMM")

As you can see, even though you have to use DAX functions, it’s still pretty easy to make date tables in Power BI. Even better now that Power BI installed icons to make it easier to create and edit date models.