Revision history [back]

Sure, here are the steps to create a calendar table in Power BI:

1. Open Power BI desktop and go to the Home tab.

2. Click on the "Enter Data" button in the External Data group.

3. In the Create Table dialog box, enter the following columns and values for the calendar table:

• Date: Enter the dates for the period you want to create the calendar table for. You can use the Excel formula =DATE(2022,1,1) + ROW(A1:A366)-1 to automatically generate dates for the year 2022. Adjust the year and number of rows as needed.
• Year: Use the Excel formula =YEAR([Date]) to extract the year from the Date column.
• Month: Use the Excel formula =MONTH([Date]) to extract the month from the Date column.
• Month Name: Use the Excel formula =FORMAT([Date],"MMMM") to get the full name of the month.
• Day: Use the Excel formula =DAY([Date]) to extract the day of the month from the Date column.
• DayOfWeek: Use the Excel formula =WEEKDAY([Date],2) to get the numeric value of the day of the week (Monday=1, Sunday=7).
• DayName: Use the Excel formula =FORMAT([Date],"dddd") to get the full name of the day of the week.

1. Name the table "Calendar" and click on the Load button to create the calendar table in Power BI.

2. Go to the Data View tab and verify that the calendar table has been created with the correct columns and values.

3. You can use the calendar table in your Power BI reports to create measures and visuals that use dates or date ranges. For example, you can use the DayOfWeek column to group data by weekdays or weekends, or the Month Name column to create a slicer that lets users filter data by month.