Power BI Calendar

A table in a calendar dimension is known as a calendar table in power bi, the function used to create such tables is calendar DAX function, basically there are two simple steps involved in the creation of a calendar table fist is to create a calculated table and then sort orders.

Power BI Calendar Table

In order to have the above result, we need to create a “Calendar” table in Power BI. In this article, we will show you how to create a “Calendar” table in Power BI. When you have a sales table or any other data table with dates, you may want to create a separate table which includes only dates, year, month, and day column so that based on the “Date” column, 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 make use of DAX function i.e. “CALENDAR.” Calendar function helps us to create the calendar table by specifying the start date and end date.

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

CALENDAR (Start Date, End Date)

The syntax is simple. We just 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 and Under “Modelling” 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 the name to it as “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 don’t know what format to put for a date, so by enclosing the DATE functionDATE FunctionThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more, we can specify Year, Month, and Day. So open the DATE DAX function now.

    Power BI Calendar (calendar date)

  6. DATE function has three elements i.e. Year, Month, and Day for this, enter the starting date year, month, and day. 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 as well. Our end date is 31st Jan 2019. For this, open the DATE function and pass the numbers accordingly.

    Power BI Calendar (End date)

  8. Ok, after this close two brackets and hit enter key. We will have the DATE column in the newly inserted table.

    Power BI Calendar (Date table)

Here you go, we have dates from 01st Jan 2019 to 31st Jan 2019.

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 and from that column if you want to build a calendar table, then that is also a possibility.

Below is the data I 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 get the start date and end date dynamically.

  • Go to “Modelling” tab and click on “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)
  • For Start Date, we cannot give the DATE function here because we don’t know exactly what is the starting date from our data table So to automatically fetch the starting date from the table, we need to use the “FIRST DATE” 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 hit the enter key. We should get “Date Calendar” from the “Data Table” automatically.
Power BI Calendar (Date Column)
  • Now from this “Date” column, we can create Year, Month, Day, Week Number, Quarter number in separate columns. Right-click on the “Data Table” and choose “New Column.”
New Column
  • This 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
  • Hit the enter key, and we should get “Year” from the date column.
Year Column
  • Similarly, extract the “Month” number from the “Date” column.
Month

Often times we need month name, so for this by using the Format function, we will select the “Date” column and format it as the only month i.e. “MMMMM.”

  • Below is the formula we have applied to get only the “Month” name.
Month Name
  • To get a week number, use the below formula.
Week Function
  • Now we need to decide 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: Power BI Calendar file can also be downloaded from the link below, and the final output can be viewed.

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

Things to Remember Here

  • Calendar table needs to be created from the DAX function CALENDAR only in power bi.
  • Start and end dates are referenced by First Date and Last Date to create a calendar table from other existing tables.
  • In Power BI, Calendar Table will be inserted as a new table only.

Guide to Power BI Calendar. Here we discuss how to create a calendar table using the DAX function in Power BI along with practical examples. You may learn more about Power BI from the following articles –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>