Build A Comprehensive Date Table In Power BI Really Fast

In this post, I’m going to show you what is probably the quickest and most effective way to create a detailed date table in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

If you want to analyze anything over time, there is likely no more important table to create for any Power BI model than a great date table.

Creating The Date Table In Power BI

A date table can be created in a few ways. In this example I use ‘M’ code within the query editor.

If you want to follow along, you can download the code within this course:

Ultimate Beginners Guide to Power BI

Look for the file that looks like this one below.

m code for date table in power bi

Now, we can dive into creating our date table.

1. Create A Blank Query

The first thing that we have to do is to create a blank query. To do this, select New Source.

new source

You’ll get a pop up window like this one below.

Just scroll down and look for the Blank Query Option. Select this option and then click Connect.

blank query option

After clicking Connect, you’ll get a blank query.

blank query

2. Using The ‘M’ Code

The next step now is to change the code that is within this query. This is where the ‘M’ Code comes in.

So select the Advanced Editor on top.

advanced editor

You’ll get an Advanced Editor pop up window with some existing text. Delete this text then copy and paste the date table code onto the Advanced Editor.

advanced editor window

You should have something that looks similar to this.

changing the code

After pasting the date table code, click Done.

3. Input Parameters

You’ll see now that we have some parameters. We can actually input some parameters for this dates table.


For the StartDate, let’s say 1st of January 2014 and for the EndDate, 31st of December 2016.

We also have an option to set the Financial Year Start Month. You can choose whatever month here but for this particular example, let’s choose July.

input parameters

After clicking Invoke, you’ll have this entire table.

date table created in power bi

4. Reviewing The Table

Let’s do a quick review of what was created for us here.

We have month and year and quarter and year.

month and year and quarter and year

We also have day of the week and week ending.

day of the week and week ending

But what is awesome is we also have this index column. Creating this can be quite difficult so having this ready for our use is really helpful.

index column

This index column, for example, can sort the month and year column. Since it is a text value, it can only sort itself alphabetically. So the index column can do the sorting for us.

Aside from that, the index column can also sort quarter and year, and of course, day and week.

And you’ll see that we also have financial year in our table so we can slice our tables by financial year.

financial year

What is amazing is that you don’t have to create all of these individually. They have all been created in one go.

5. Indicate The Name

Of course, we need to write the name of this table.

So, on the right side you’ll see a box for the name under properties. There you can write Dates.

naming the table

6. Close The Query Editor

To finally execute all the changes we made, click Close & Apply at the upper left corner of your query editor.

close and apply

7. Integrate The Date Table Into The Data Model

Now we can integrate our date table into our data model.  To do that, we just need to connect from date to the order date.

integrating the date table in power bi data model

And we now have our complete data model. Quick and easy.

complete data model

Why Build A Relationship Over To The Fact Table

I rarely, if ever, run any time analysis over date tables in fact tables.

I always create a date table and then build a relationship over to the fact table (like a sales data table).

Why should you do this? Well, there are a few reasons.

But the key is learning here is how to create a really good date table, fast.

You want to make sure that you have all the right code in place to do this over and over again.

***** Related Links*****
How To Create Unique Financial Year Quarters In Power BI
Filter Your Data By Unique Financial Years & Quarters – Power BI Modeling Technique
Month to Date (MTD) To Today’s Actual Date In Power BI Using DAX


So we have just run through a very quick way to create a detailed date table in Power BI.

I personally think this is a really efficient way to do it, because you can make other small changes to the table within the query editor at the same time.

Hopefully you can utilize this in your own work for some compelling time intelligence analysis.

If you haven’t downloaded the code yet, again you can get it within this course.

Ultimate Beginners Guide to Power BI



Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More