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

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Did you know that you can create a date table in Power BI? 

When comparing Power BI to Microsoft Excel, a lot of people have expressed their frustration of not being able to modify, edit, or even add new elements on Power BI.

Though totally understandable, it’s a misunderstanding. Sure, it’s not as easy and intuitive as in Excel. But you definitely can!

If so, how do you create one? What if you need a date reference or a calendar for your existing data? Do you have to go back to Excel and create another set of columns?

In this article, we’ll show you simple steps you can follow to create a date table in Power BI. Some of the contents in this article relate to Data Analysis Expressions or DAX.

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Power BI Online (also called "Power BI Service").

Let’s get started! 📅

Introduction to Date Table

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:

Store table of Retail Analysis Sample in the data view

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.

Creating a date table in Power BI Desktop isn’t only possible — it’s super easy to do even without DAX knowledge.

Kasper Langmann, Co-founder of Spreadsheeto

Making a Date Table Based on Existing Data Source

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:

The data view icon on the left pane of Power BI Desktop

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

The ‘New Table’ button on the Power BI 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”:

Naming the new 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.

If you have a data source with a date range, all you have to do is reference it.

Kasper Langmann, Co-founder of Spreadsheeto

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! 🎉

Creating a Date Table Without Data Source

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.

One of the ways to do that is by writing the date itself. Power BI is smart so it could detect your date as long as it’s clear.

Kasper Langmann, Co-founder of Spreadsheeto

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:

A date table for the year 2013

Pretty easy, right? 😁

Adding New Columns to Your Date Table

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.

The ‘New Column’ button on Power BI ribbon

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])
The “Year” column in date table

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")
The “Month” column in date table

There are still a lot more you can add. If you would like to know more, just visit Microsoft’s DAX functions for date and time.

Kasper Langmann, Co-founder of Spreadsheeto

Wrapping things up…

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.

If you got confused along the way, just copy and paste the function codes above in this tutorial. That way, you’ll be able to follow it every step of the way. 😊

Kasper Langmann, Co-founder of Spreadsheeto