Creating a date table in Power BI

To create a date table you need to create a new data table which I usually call timeline.

Click on new table in the top menu.

Create the date column.

This will create the table filled with records with the date range specified.

Date = Calendar(Date(2010, 1, 1), Date(2022,12,31))

Add some custom columns

Right click the date table and select New Column, once for each of the DAX statements. Ignore the commentary statements starting with #.

# Add Year
Year = Format('Date'[Date], "yyyy")
# Add Month
Month = Format('Date'[Date], "mm")
# Add Yearly - Quarterly
Quarter = Year('Date'[Date]) & "-Q" & Format('Date'[Date], "q")
# Add SortOrder
SortOrder = Format('Date'[Date], "yyyy-mm")
#  Add Week
Week = WEEKNUM('Date'[Date],2)
# Add Week Start Date
Week Start Date = 'Date'[Date] - WEEKDAY('Date'[Date],2)+1
# Add Week Start Date
Week Start Date = 'Date'[Date] - WEEKDAY('Date'[Date],2)+7

When you are finished, It should look similar to the below screenshot.

After we have finished setting up the table we need to set up the relationship with the normal tables.

Create a duplicate column

Open up the power query editor by clicking data transform. Right click the date column your want to create the relationship with and select duplicate column and name it date-timeline.

Change the field format

Set the field format for the date-timeline column to the exact same as the timeline table but remove the time.

Create the relationship

Create the relationship with the timeline table and the new date-timeline field.

Now you are ready to use the timeline table to query your data.