A very common requirement in data analysis is working with Dates, a key tool for handling this is a Date table as this centralizes the concept of dates for your organization and allows for some complex filtering and display within your reports.
There are a couple of steps for building a date table and once completed we will have a highly dynamic range of dates for PowerBI.
Outline
- Identify the Date Ranges
- Pick a Fixed Starting Date
- Build a Dynamic End Date
- Creating the Date Table
- Add a New Table
- Add the First Column
- Basic Date Columns
- Date Labels
- Current Date Options
Identify The Date Ranges
Before we start up PowerBI we need to work out the range of dates that make sense for our report, ideally, we want to create a range that is as large as we need to capture every date, but still as small as possible to keep performance high.
Adding all the dates for a hundred years sounds good at first but the performance quickly goes down hill as we add calculated columns on the table.
Pick a Fixed Starting Date
There are two main parts to figure out, the first is the date of the year that you want to start your date table on. Most commonly this is the start of the Fiscal Year for your organization; if you’re not sure what this is check with the Accounting Department they should be able to tell you, most often this will be Jan 1.
The second part is to figure out what year to start the date table on, this will very a lot with what the report is trying to accomplish, and normally will be back at least 1 year if not a few more.
For this demonstration, I’ll be using 1 Jan 2012.
DATE(2012,1,1)
Using the DATE formula is the best way to go as this will avoid a number of issues that can occur with local date formats that can come up from using a date string instead and changing computers.
Building a Dynamic End Date
We can create a Dynamic End Date that will grow as we advance in time, this is because we can pass a function to the End Date Parameter of the CALANDER function.
In nearly all cases just adding to the end of the next year will be enough for most reporting needs, if there are cases where you need more then the current and next year you can easily just add a +2 (or more) instead of a +1.
In most cases, you will want to use the Day before the Start of the Fiscal Year or the Fiscal Year End, again your Accounting Department can help you with this if you are not sure, in most cases, this will be December 31.
In this case, I will use December 31 as the year end
DATE(YEAR(TODAY())+1,12,31)
If your Fiscal Year end is February you can run into some challenges with leap years and hard-coding of dates; we can use the EOMONTH function to handle this.
DATE(YEAR(TODAY())+1,12,EOMONTH(DATE(YEAR(TODAY())+1,12,1),0))
Create the Date Table
Now that we have established the parameters of the how long the date table will be created for we can start PowerBI and add it to our model.
Add a New Table
Open a New Power BI Model, and switch to the model editor and select New Table
In the Formula, Bar adds in the details for the dates we identified above.
Date = CALENDAR(DATE(2012,1,1),DATE(YEAR(TODAY())+1,12,31))
The CALENDAR function is specifically designed to create a table like this and takes the Start and End date we outlined above. Adding the Dynamic Values will allow it to expand dynamically.
This will populate the Date table which we will use for all the rest of the future calculations and because of the Dynamic End Date. You should also switch the Data Type to Date instead of the default Date/Time, and the Format to one you like (I prefer yyyy-MM-DD).
Now we can build up a range of helpful date formulas, many of them are based on simple formatting and make reporting dates a snap.
Adding a new Column
The Syntax for a formula in DAX is “Column Name = Expression to Evaluate” this makes adding columns in PowerBI quick and painless. The part after the = sign will work if you are using Excel to build the Date table as well and you’ll need to rename each column.
Basic Date Functions
These are the basic functions for formatting dates, and ordering information.
Year = FORMAT([Date],"YYYY") Month = FORMAT([Date],"MM") Day = FORMAT([Date],"DD") DayOrder = FORMAT([Date],"W - DDD") Weekday = FORMAT([Date],"W") Week = FORMAT([Date],"WW") Quarter = FORMAT([Date],"Q") YearMonth = FORMAT([Date],"YYYY-MM") MonthSort = FORMAT([Date],"mm-MMM") DateKey = FORMAT([Date],"yyyymmdd")
Date Name Functions
It is helpful for a number of report labels to have access to the long and short versions of the various date aspects.
WeekdayNameShort = FORMAT([Date],"DDD") WeekdayNameFull = FORMAT([Date],"DDDD") QuarterName = "Q" & FORMAT([Date],"Q") MonthNameShort = FORMAT([Date],"MMM") MonthNameFull = FORMAT([Date],"MMMM")
Current Date Functions
These are very helpful for a wide range of functions of current reporting and other dynamic scenarios. These are returned as boolean (true/false) values, so once you create them you’ll want to change the data type to true/false.
IsCurrentDay = IF([Date]=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),1,0) IsCurrentWeek = IF(FORMAT([Date],"YYYY-WW")=FORMAT(TODAY(),"YYYY-WW"),1,0) IsCurrentMonth = IF(DATE(YEAR([Date]),MONTH([Date]),1)=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(1)),1,0) IsCurrentYear = IF(YEAR([Date])=YEAR(TODAY()),1,0)
There are a number of areas to extend and add more functions if they are required for your reports such as IsYesterday, IsTomorrow, IsLastWeek, IsNextWeek.
Summary
The steps above will create a simple but powerful date table that will last the test of time and not need to be updated in the future, which is very important once a model gets out in the wild or handed off to someone else.
There is a lot more we can do with the date table which I’ll cover in future posts, such as adding Hierarchies for reporting, adding more custom functions.
I hope this helps your reporting efforts, let me know if there is anything I missed in the comments!
Streets Ahead 