Creating Custom Calendar Table in Different Ways

A calendar table is a useful tool in data modeling that can be used to analyze data over time periods. In this article we are going to create calendar table in 2 different ways:

1. Using Power Query
2. Using DAX

Using Power Query

Calendar table can be created in Power Query, a data transformation and cleansing tool within Power BI. Here are the steps to create a calendar table in Power Query:

Step 1: Open Power Query Editor

Open Power Query Editor Open Power BI and click on the “Transform Data” button to open Power Query Editor.

Step 2: Create Blank Query

Create a new query In Power Query Editor, click on “New Source” and select “Blank Query” from the drop-down menu.

Step 3: Open ‘Advanced Editor’

Step 4: Write M code

Enter the M code given below in the advanced editor:

let StartDate = #date(2020, 1, 1), // Enter your own start date
EndDate = #date(2022, 12, 31), // Enter your own end date
NumberOfDays = Duration.Days(Duration.From(EndDate – StartDate)),
Source = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Date”, type date}})
in
ChangedType

This M code defines the start and end dates for the calendar table, creates a list of dates, and then transforms the list into a table.

Note: Replace the start and end dates in the code with your own desired dates.

Step 5: Name the query and save

Name the query “Calendar Table” or any other name you prefer and save the query. Click on “Close & Apply” to save the query.

Step 6: Use calendar table in the report

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

Using DAX

Here are the steps to create a calendar table using Data Analysis Expressions (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 that includes 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 use can only use calendar function only which is Table Name = Calendar(, ) The output of the Calendar function is a table with one column which includes all dates between the start and end date, with one day at 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 that have 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 Power Query or 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.