Power BI Calendar

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.

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."

   
   

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

   
   

3. Now, give it the name "Calendar Table."

   
   

4. Now, open the 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.

   
   

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.

   
   

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.

   
   

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

   
   

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

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.

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."

• Open the CALENDAR function first.

• 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.

• For this DAX function, give the date column name from the "Data Table."

• For End Date, use the LASTDATE DAX function and give the DATE column reference from “Data Table.”

• Close two brackets and press the "Enter" key. We should get the "Date Calendar" from the "Data Table" automatically.

• 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."

• 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.

• For this function, reference the "Date" column from the "Date Table."

• Press the "Enter" key. We should get "Year" from the "Date" column.

• Similarly, extract the "Month" number from the "Date" column.

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.

• To get a week’s number, use the below formula.

• 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 #."

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.

• 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.