Create "Date" Table using DAX (Data Analysis Expression) functions

Date Table is a common table to reference with multiple tables where date columns used (like Invoice Date or Purchase Dates) in Power BI.

Data Analysis Expressions (DAX) is the native formula and query language for Microsoft PowerPivot, Power BI Desktop and SQL Server Analysis Services (SSAS).

To create a common data table there are 2 DAX function available CALENDARAUTO() and CALENDAR()

CALENDAR() function to create table with range of dates based for give start and end dates.
CALENDARAUTO() function create table with range of dates that are automatically determined from the dataset.

To create a table in Power BI Desktop, go to the Table tab on the ribbon. Select New Table, and then enter in the following DAX formula:

Code

Dates= CALENDAR(DATE(2021, 1, 1), DATE(2021, 12, 31))
or
Dates= CALENDARAUTO()

This will create a date table with one column called "Date".

To add additional columns to the table like Year, Month, Month Name etc., use below DAX queries by clicking on "Add Column".

Code

Year = YEAR(Dates[Date])
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date].[Day], "DDDD")

Next