How to Create a Calendar Table in Power BI
Jun 16, 2023
Power BI has emerged as a game-changer in the realm of business intelligence and data analytics. Its user-friendly interface, extensive connectivity options, and powerful visualization capabilities make it a valuable tool for organizations across industries. By harnessing the potential of Power BI, businesses can unlock the true value of their data, uncover hidden insights, and make data-driven decisions that propel them toward success in a rapidly evolving business landscape.
What is Power BI?
Power BI is a cloud-based business intelligence and data visualization platform that allows users to connect to a wide range of data sources, combine data from multiple sources, and create interactive reports and visualizations. It offers a user-friendly interface with drag-and-drop functionality, making it accessible to both technical and non-technical users.
In this blog post, we are going to explore one of Power BI's many functionalities: How to create a calendar table using Power BI. Follow these easy step-by-step tutorials to create one.
Step 1 – Open your Power BI Desktop App on your computer.
Step 2 – Under the Modeling tab, click on “New Table” as shown in the image below.
Step 3 – Give the table the name of your choice. Here I used “D_Calendar” as shown in the image below. After giving your table a name, press equal to (=) and type one of the Data Analysis Expression (DAX) - “Calendar”. It provides you with the intelligence which you can see in the image below.
Step 4 – Inside the “Calendar” function you include a “Date” function that contains the year, month, and day for both StartDate and EndDate. In the image below, I used January 1st, 2015, as the StartDate and December 31st, 2022 as the EndDate. You can copy the function under the image below.
D_Calendar = CALENDAR(
DATE(2015,1,1),
DATE(2022,12,31))
Step 5 – Click on “New Column” as shown in the image below.
Step 6 – Give your Column the name “Year” to extract the Year from the Date. See the function in the image below. You can also copy the function under the image directly to Power BI.
Year = FORMAT(
D_Calendar[Date],
"YYYY")
Step 7 – Repeat “Step 5” and give your Column the name “Month_Name” to extract the Month Name from the Date. See the function in the image below. You can also copy the function under the image directly to Power BI. 
Month_Name = FORMAT(
D_Calendar[Date],
"MMM")
Note: The above function would give you the Month name in short like this “Jan” but if you want it in full, include one more M to the function.
Step 8 - Repeat “Step 5” and give your Column the name “Day” to extract the Day number from the Date. See the function in the image below. You can also copy the function under the image directly to Power BI.
Day = FORMAT(
D_Calendar[Date],
"DD")
Note: If you want the Day Name, add two more “DD” to the above functions.
Step 9 - Repeat “Step 5” and give your Column the name “Fiscal Year” to get the Fiscal Year. See the function in the image below. You can also copy the function under the image directly to Power BI.
Fiscal Year = IF(
MONTH(D_Calendar[Date] >=1),
YEAR(D_Calendar[Date]),
YEAR(D_Calendar[Date]-1))
Note: The 1 at the end of this MONTH(D_Calendar[Date] >=1) is the Start Month.
Step 10 - Repeat “Step 5” and give your Column the name “Quarter” to get the Quarter. See the function in the image below. You can also copy the function under the image directly to Power BI.
Quarter = "Q" & FORMAT(
D_Calendar[Date],
"Q")
Note: Use this function “CALENDARAUTO” if you have a date already in your data in Step 4 and follow the other steps accordingly. The function would automatically create a date table based on the StartDate and EndDate in your date column. Also, if your fiscal year does not start in January, kindly input the number of the month in the function. For example, if your fiscal year starts in March, your function would be, CALENDARAUTO(3).
Learn more functionalities in Power BI and Unlock your potential or your team's potential to extract insights and drive data-informed decisions by mastering the development of interactive reports and analytics using Power BI.
