POWER BI: Modeling

Date Table

What is a date table and why is it needed?

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.

How to make a date table:

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:

  • In Power BI Desktop: Select the "Modeling" Tab
  • Select "New Table"
  • Paste in the CaldendarTable above

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.


Building Up Your Calendar Table:

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.

Desciption Expression Used
Return a custom date format: (Feb/01/2017) FORMAT([Date], "mmm/DD/YYYY")
Return Year YEAR([Date])
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.

See also:

Up Next: Handy Date Functions


Dash-Intel is a Power BI and Tableau resource site for data visualization and building BI dashboards.

Data Analyst & Consultant

Copyright 2015-2023 Dash-Intel.com Terms