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.
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.
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.
- Open blank Power BI file and Under “Modelling” tab click on “New Table.”
- Now, it will ask you to name the table.
- Now give the name to it as “Calendar Table.”
- Now open the CALENDAR DAX function.
- 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., we can specify Year, Month, and Day. So open the DATE DAX function now.
- 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.
- 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.
- Ok, after this close two brackets and hit enter key. We will have the 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 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.
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 the CALENDAR function first.
- 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.
- 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 TableData TableA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section..”
- Close two brackets and hit the 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 the “YEAR” function to extract the year from the date.
- For this function, reference the “Date” column from the “Date Table.”
- Hit the 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 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.
- To get a week number, use the below formula.
- 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 #.”
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 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 –