Why do we need a Calendar Lookup table in the Power BI data model?

A Calendar Lookup table can provide you with more information about the date. What’s more, it can make sure the consistency of the date. So it’s highly recommended to add the date table in your data model of Power BI. I’ll show you how to create a date table by Power Query in this blog.

 

Steps to create the Calendar Lookup table by Power Query

a) Make sure your data contain the date field

Usually, we will use our data to left-join the date table in the data model. So, make sure your data contain the date field.

In this blog, I use the Sample – Superstore Data. You can download it from here if you want to do the same thing as me.

 

b) Create a Blank Query

After importing the Superstore dataset, create a blank query.

Figure 1. Chart Blank Query

 

c) Copy & Paste the date table Query

Click the advanced editor and paste the Query:

You can download the Query from here.

Figure 2. Paste the Date Table Query

 

d) Enter Parameters

Since the Superstore dataset’s date is ranged from 2019 to 2022, so I use the same range in the StartDate and EndDate. FYStartMonth means the start month of the financial year.

After entering the parameters, you can change your Query name to Date (Optional).

Figure 3. Parameter Configuration

 

Then you get your date table. You can notice that the date range is from the start month to the end month in your entering parameters.

Figure 4. The Date Table

 

e) Edit your data modeling

Be careful of the connecting!

Since we want to use our Orders table to left-join the Date table, make sure you drag the Orders Date field – <Order Date> to connect the Date field – <Date> in the Date Table.

GIF 1. Connect the tables

 

f) Test your Connection

It works! The fields of the Date Table can be used.

GIF 2. Test Connection

 

If there are any problems, please feel free to point them out. Besides, you can reach out to me on LinkedIn. I will try my best to answer your questions about Tableau or Alteryx.

 

Joe Chan
Author: Joe Chan

Joe has an IT background with a master's degree in UNSW, majoring in AI and Data Science. During his studies, he realized Data is one of the most valuable assets a business can have and potentially has a tremendous impact on its long-term success. After graduation, his desire to level up his data analytics skills led him to join The Data School. He is interested in Data Wrangling, Data Visualization, and Machine Learning, eager to be a great Data Analyst to help businesses grow.