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 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 “Date” column we can create a relationship between two tables later on.
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 CALENDAR function in Power BI.
The syntax is simple we just need to provide 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.
- Step 1: Open blank Power BI file and Under “Modelling” tab click on “New Table”.
- Step 2: Now it will ask you to name the table.
- Step 3: Now give the name to it as “Calendar Table”.
- Step 4: Now open CALENDAR DAX function.
- Step 5: Here is a catch i.e. we don’t know what format to put for a date, so by enclosing DATE function we can specify Year, Month, and Day. So open DATE DAX function now.
- Step 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.
- Step 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 DATE function and pass the numbers accordingly.
Ok, after this close two brackets and hit enter key, we will have DATE column in the newly inserted 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 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.
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”.
- Open CALENDAR function first.
- For Start Date, we cannot give 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 “FIRST DATE” function.
- For this DAX function give the date column name from the “Data Table”.
- For End Date use the LAST DATE DAX function and give DATE column reference from “Data Table”.
- Close two brackets and hit enter key we should get “Date Calendar” from the “Data Table” automatically.
- 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”.
- This will ask you to name the column and name the column as “YEAR” and open “YEAR” function to extract the year from the date.
- For this function reference the “Date” column from the “Date Table”.
- Hit enter key and we should get “Year” from the date column.
- Similarly, extract the “Month” number from the “Date” column.
Often times we need month name, so for this by using 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 “Month” name.
- To get week number use below formula.
- Now we need to decide the “Quarter” number so for this we need to use “IF” condition, so below is the formula to get “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.
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 calendar table from other existing tables.
- Tn 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 DAX function in Power BI along with practical examples. You may learn more about Power BI from the following articles –