Probably the most essential table you need in your Power BI model is a date or calendar table.
You join your date table from any other table containing dates, such as your main Fact Sales table.
The advantage of using a calendar table is that it contains associated fields such as Year, Month, Start of Month, Start of Quarter, and relevant Financial Periods that will be bespoke to the business.
The date table should be stored in a data flow so everyone can use it. Here we create one using DAX, which could be exported and imported as a table in the Power Query service. You could also create a date table with Power Query directly. Creating one in DAX gives us practice with Time Intelligence functions.
The steps to create a date table are as follows:
1. Open Table View and select ‘New Table’
2. Start by creating the first column which will be our date key. We can use the CALENDAR() function to auto-create a list of dates in the table. The date range should include the minimum date relevant to your date up to any dates in the future. You could use TODAY() as dates up to today, for the end date, but sometimes for example with forecasting, it can be useful to have future data.
Of course, a big table may slow performance, so just bear that in mind.
Date =
VAR DateStart = DATE(2007, 01, 01)
VAR DateEnd = DATE(2030, 12, 31)
RETURN
CALENDAR(DateStart, DateEnd)3. Once you have your first date column setup which will be the key to joining your other tables, you can then start any other useful columns.
Here are some for starters:
Year = YEAR('Date'[Date])
Month = MONTH('Date'[Date])
Day = DAY('Date'[Date])
Quarter = QUARTER('Date'[Date])
StartOfYear = STARTOFYEAR('Date'[Date])
StartOfQuarter = STARTOFQUARTER('Date'[Date])
StartOfMonth = STARTOFMONTH('Date'[Date])
DayOfWeek = WEEKDAY('Date'[Date], 2)
Be careful with the day of the week number. It’s always best to double-check against another calendar.
The default is 1 for Sunday, to set the first day of the week, use WEEKDAY(‘Date'[Date], 2).
Once you have the day number setup, which can be useful in itself, you can then create a column for the day name:
DayName =
SWITCH('Date'[DayOfWeek],
1, "Mon",
2, "Tue",
3, "Wed",
4, "Thu",
5, "Fri",
6, "Sat",
7, "Sun")Once we’ve finished creating our data table, we can then join it within the model to the relevant tables, such as the Contoso FactSales table which has a date key. Note, that the Date field in the Date table is unique.
