Before you start using date and time intelligence functions in DAX and Power BI you should create a seperate date table. A date table or calendar table is a table which contains only date related data. For instance, every calendar table must have a date column which contains unique dates from the start and end date specified. (The start and end dates can later be changed if you like). If your data is at the day level of granularity (i.e you look at daily sales) then it is best practice to have a date table even if you are not using time intelligence functions. It keeps your data organized and you can create date related columns such day of week, or specify holidays in your date table. This date table is joined with the other tables in your data model under relationship view. It is easy and quick to set up. Once done you can easily compare, group, filter and summarize your data for any specific period in time.
Create a new table in Power BI and in the formula bar type or paste in the following:
CalendarTable = CALENDAR(DATE(2010,1,1),DATE(2020,12,31))
This creates a date table with a unique set of dates from Jan 1st 2010 to Dec 31st 2020. It will also create a year/quarter/month/day hierarchy which enables you to drill up or down on specific data in drill down mode.
Steps to create a Calendar Table:
You may also need to create a relationship between the newly created CaldendarTable and the table that has all your data. Under (but not in) file, select the relationship icon and connect the two tables together via the date columns. This is a simple drag and drop.
Once this table is added you can create new columns in this table to build your calendar table. The table below shows some new columns you can add.
|Return a custom date format: (Feb/01/2017)||FORMAT([Date], "mmm/DD/YYYY")|
|Return Month Number||FORMAT([Date], "MM")|
|Return Year then Month Number: (2017/02)||FORMAT([Date], "YYYY/MM" )|
|Return Week of Year Number||weekNumber", WEEKNUM([Date])|
|Return Year then Month||FORMAT([Date], "YYYY/mmm")|
|Return Short Month Name||FORMAT([Date],"mmm")|
|Return Long Month Name||FORMAT([Date],"mmmm")|
|Return Day of Week (long format)||FORMAT([Date],"dddd"),|
|Return Day of Week (short format)||FORMAT([Date],"ddd"),|
|Return Quarter: (Q1)||FORMAT([Date],"Q"),|
|Return Year Then Quarter: (2017/Q1)||FORMAT([Date],"YYYY") & "/Q" & FORMAT([Date],"Q")|
|Return Date associated with Week Number (Start Monday)||DATE(YEAR([Date]),1,-2) -WEEKDAY(DATE(YEAR([Date]),1,3)) + WEEKNUM([Date])*7,|
|Return Date associated with Week Number (Sunday)||DATE(YEAR([Date]),1,-2) -WEEKDAY(DATE(YEAR([Date]),1,3)) + WEEKNUM([Date])*7 + 6,|
The last two rows are useful when you want to group data week over week. While the WEEKNUM function can be used to group your yearly sales by week, it is hard to read. Rather then seeing why sales dropped in say week 16 for year 2016, you can instead see there was a drop on the week of Monday April 18th. You can also change this to Sunday by adding 6, to get week ending on Sunday April 24th.
Up Next: Handy Date Functions