Power BI template with built in Date dimension

If you are serious about building Power BI solutions, you definitely need a Date dimension in your data model. Reza Rad had written a fantastic blog about why need this, you can find it here. Isn’t it good that you can have a template file that already has a built in Date table and you can use this as a starting point to build your data model. As you can see below, this table has all the dates from a starting date (defined by you) and up until yesterday. Plus it is dynamic so it will update as time goes by when you refresh your data model. I have built a Power BI template file so when you open it or import it you will have a built in Date table. You will find the download link at the end of this post.

Date.JPG

If you open the query editor and go to Advanced Editor, you will see the underlining M code as below

//Create Date Dimension
(StartDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),
Date.Day(StartDate)),
EndDate = Date.From(DateTime.LocalNow()),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate – StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, “Year”,
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , “Quarter”,
each “Q” & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , “Week Number”,
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, “Month Number”,
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , “Month”,
each Date.ToText([Date],”MMMM”)),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , “Day of Week”,
each Date.ToText([Date],”dddd”))
in
DayOfWeek

The above M code creates a function and by invoking it after you specify a start date, you will get a nice date table for your data model. If you need additional columns such as fiscal year you can either amend the M code to add it or you can do so after loading this table into your model first. Entirely up to you.

Date2.JPG

You can download the Power BI template file here

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s