Power BI Calendar

Updated on January 2, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

A table in a calendar dimension is known as a calendar table in Power BI. The function used to create such tables is the CALENDAR DAX function. There are two simple steps involved in creating a calendar table. The first is to create a calculated table and then sort orders.

Power BI Calendar Table

To have the above result, we need to create a calendar table in Power BI. This article will show you how to create a calendar table in Power BI. For example, when you have a sales table or any other data table with dates. You may want to create a separate table that includes only dates, year, month, and day columns based on the “Date” column. Then, we can create a relationship between the two tables later on.

Power-BI-Calendar

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Calendar (wallstreetmojo.com)

How to Create Simple Calendar Table in Power BI

To build a calendar table in Power BI, we need to use the DAX function, “CALENDAR.” The CALENDAR function helps us create the calendar table by specifying the start and end dates.

Below is the syntax of the CALENDAR function in Power BI.

CALENDAR (Start Date, End Date)

The syntax is simple. We need to provide a Start Date and End Date.

Now, we will create a calendar table from 01st Jan 2019 to 31st Jan 2019.

Follow the below steps to create a calendar table in Power BI.

  1. Open blank Power BI file. Then, under the “Modeling” tab, click on “New Table.”


    Power BI Calendar (New Table)

  2. Now, it will ask you to name the table.


    Power BI Calendar (Table)

  3. Now, give it the name “Calendar Table.”


    Power BI Calendar (Calendar Table)

  4. Now, open the CALENDAR DAX function.


    Power BI Calendar (CALENDAR DAX function)

  5. Here is a catch, i.e., we do not know what format to put for a date. So, by enclosing the DATE function, we can specify “Year,” “Month,” and “Day.” So, open the DATE DAX function now.


    Power BI Calendar (calendar date)

  6. The DATE function has three elements: year, month, and day. First, enter the starting date, year, month, and day. For example, since our starting date is 01st Jan 2019, enter the arguments as follows.


    Power BI Calendar (Elements)

  7. Once the start date is specified similarly, we need to specify the end date. Our end date is 31st Jan 2019. For this, open the DATE function and pass the numbers accordingly.


    Power BI Calendar (End date)

  8. After this, close two brackets and press the “Enter” key. We will have the DATE column in the newly inserted table.


    Power BI Calendar (Date table)

We have dates from 01st Jan 2019 to 31st Jan 2019.

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

Build Calendar From Data Tables in Power BI

The above one we have seen was entering the start date and end date manually, but when we have a date column in the Data Table. So, if you want to build a calendar table from that column, that is also a possibility.

Below is the data we have uploaded to Power BI. You can download the Excel workbook to use it.

You can download this Power BI Calendar Excel Template here – Power BI Calendar Excel Template
Power BI Calendar (Data)

In this table, we have different date ranges. So, by using DAX functions, we will dynamically get the start and end date.

  • Go to the “Modeling” tab and click “New Table.” Give the name to the table as “Date Table.”
Power BI Calendar (Data Table)
  • Open the CALENDAR function first.
Power BI Calendar (Data table Calendar)
  • We cannot give the DATE function here for Start Date because we do not know the starting date from our data table. So, to fetch the starting date from the table, we need to use the “FIRSTDATE” function.
Power BI Calendar (First Date)
  • For this DAX function, give the date column name from the “Data Table.”
Power BI Calendar (data(date))
Power BI Calendar (LastDate)
  • Close two brackets and press the “Enter” key. We should get the “Date Calendar” from the “Data Table” automatically.
Power BI Calendar (Date Column)
  • From this “Date” column, we can create year, month, day, week, and quarter numbers in separate columns. Then, right-click on the “Data Table” and choose “New column.”
New Column
  • It will ask you to name the column and name the column as “YEAR” and open the YEAR function to extract the year from the date.
Year syntax
  • For this function, reference the “Date” column from the “Date Table.”
Year table
  • Press the “Enter” key. We should get “Year” from the “Date” column.
Year Column
  • Similarly, extract the “Month” number from the “Date” column.
Month

Often we need a month’s name. So by using the FORMAT function, we will select the “Date” column and format it as the only month, “MMMMM.”

  • Below is the formula we have applied to get only the “Month” name.
Month Name
  • To get a week’s number, use the below formula.
Week Function
  • Now, we need to decide on the “Quarter” number. So for this, we need to use the “IF” condition, so below is the formula to get “Quarter #.”
Power BI Calendar (quarter)

This function is very similar to the one we use in MS Excel.

Note: We can also download the Power BI calendar file from the link below. We can view the final output.

You can download this Power BI Calendar Template here – Power BI Calendar Template

Things to Remember Here

  • We must create the calendar table from the DAX function CALENDAR only in Power BI.
  • The first and last dates reference the start and end dates to create a calendar table from other existing tables.
  • In Power BI, the calendar table will only be inserted as a new table.

This article is a guide to Power BI Calendar. Here, we discuss creating a calendar table using the DAX function in Power BI and practical examples. You may learn more about Power BI from the following articles: –