Create Calendar Table Using DAX

A calendar table is a useful tool in data modeling that can be used to analyze data over time periods. It can be created in Power BI using Data Analysis Expressions (DAX).

Here are the steps to create a calendar table using DAX:

Step 1. Open the DAX formula bar

Open Power BI and go to the "Modelling" tab. Click on "New Table". Then DAX formula bar will appear.

Step 2. Enter the DAX formula

In the formula bar, enter the DAX formula:

Calendar =
ADDCOLUMNS (
    CALENDAR (DATE(2018,1,1),DATE(2019,12,31)),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMMM" ),
    "Day of Week Number", WEEKDAY ( [Date] ),
    "Day of Week Name", FORMAT ( [Date], "DDDD" ),
    "Quarter", "Q" & INT((MONTH([Date])-1)/3)+1
)

This DAX formula creates a calendar table with columns for the date, year, month number, month name, day of the week number, day of the week name, and quarter.

Note - If you want just one column which includes all dates, users can only use the calendar function. Table Name = Calendar(<start date>, <end date>)

The output of the Calendar function is a table with one column that includes all dates between the start and end date, with one day in each row. 

For example: Calendar = CALENDAR(DATE(2018,1,1),DATE(2019,12,31))

Step 3. Name and save the table

Press "Enter" to save the table. Name the table "Calendar Table" or any other name you prefer.

Step 4. Use the calendar table in Power BI

Once the table is saved, the calendar table can be used in Power BI by creating relationships with other tables with date columns. This allows users to analyze data over time periods such as days, weeks, months, or years.

In conclusion, creating a calendar table using DAX is a simple process that can be done in a few steps. The resulting table can be a valuable tool in data modeling and analysis in Power BI.