Martin's Blog

Unless your source data contains no dates at all, every Power BI model should have a Date entity. Chances are good that you’ve ignored a built-in option in Power BI Desktop that’s enabled by default (image below).

While the “Auto Date/Time” option isn’t the worst thing you could do in Power BI Desktop (i.e. the techies won’t swear profusely in your direction), hovering over the information icon tells you exactly why you probably shouldn’t use it most of the time.

This option (when enabled), will create a separate date/time table for each date/time attribute in your data model.

Why is it a bad idea?

If you have 10 date attributes in your model this option will cause you to have 10 hidden Date tables just to enable month, quarter and year aggregations in your visualizations. That’s a lot of extra stuff that will bloat the size of your model (see the proof here).

For small data sets the bloat and possible performance hit may be insignificant, but you will agree that it is not a very efficient way of doing things. I can understand why the Power BI product team did this, but it is unfortunately one of those “features” that promote bad habits on our end. I personally would rather have them include a Date entity as I explain below…

A better way

If your source system does not contain a Date entity or dimension, a better way is to create a such an entity with Power Query (aka “M”). By creating your own, you can add more attributes than just year, quarter and month…and optimize the size and performance of your model at the same time.

Not a Power Query expert? You don’t have to be. Others in the community have already shared their versions, and I will share my own modified version that you can copy and use for yourself. I’ve used the examples of Matt Masson and Chris Webb as the basis of my own Date entity. You can find their versions here and here.

Step 1: The date function

The date function takes a start and end date as parameters, and iterates through the dates when invoked to generate the Date entity:

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
 let
 DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
 Source = List.Dates(StartDate,DayCount + 1,#duration(1,0,0,0)),
 TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
 ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
 RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
 InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
 InsertQuarter = Table.AddColumn(InsertYear, "Quarter of Year", each Date.QuarterOfYear([Date])),
 InsertMonth = Table.AddColumn(InsertQuarter, "Month of Year", each Date.Month([Date])),
 InsertDay = Table.AddColumn(InsertMonth, "Day of Month", each Date.Day([Date])),
 InsertDayKey = Table.AddColumn(InsertDay, "Date Key", each [Year] * 10000 + [Month of Year] * 100 + [Day of Month]),
 InsertMonthName = Table.AddColumn(InsertDayKey, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
 InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])),
 InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter", each "Q" & Number.ToText([Quarter of Year]) & " " & Number.ToText([Year])),
 InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Day of Week", each Date.DayOfWeek([Date]) + 1),
 InsertDayName = Table.AddColumn(InsertDayWeek, "Day of Week Name", each Date.ToText([Date], "dddd", Culture), type text),
 InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date], Day.Saturday), type date),
 InsertRelativeDateOffset = Table.AddColumn(InsertWeekEnding, "Relative Date Offset", each([Date] - Date.From(DateTime.LocalNow()))),
 InsertRelativeWeekOffset = Table.AddColumn(InsertRelativeDateOffset, "Relative Week Offset", each (Number.From(Date.StartOfWeek([Date], Day.Saturday)) - Number.From(Date.StartOfWeek(DateTime.LocalNow(), Day.Saturday))) / 7),
 InsertRelativeMonthOffset = Table.AddColumn(InsertRelativeWeekOffset, "Relative Month Offset", each (((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(DateTime.LocalNow())) + Date.Month(DateTime.LocalNow())))),
 InsertRelativeYearOffset = Table.AddColumn(InsertRelativeMonthOffset, "Relative Year Offset", each(Date.Year([Date])) - Date.Year(DateTime.LocalNow())),
 InsertMonthKey = Table.AddColumn(InsertRelativeYearOffset, "Month Key", each [Year] * 100 + [Month of Year]),
 InsertQuarterKey = Table.AddColumn(InsertMonthKey, "Quarter Key", each [Year] * 10 + [Quarter of Year]),
 InsertCrossJoinID = Table.AddColumn(InsertQuarterKey, "CrossJoin ID", each 1)
 in
 InsertCrossJoinID
in
 CreateDateTable

I’m not going to explain each line in detail, but want to point out a few additional and/or different things I like to do:

 InsertMonthKey = Table.AddColumn(InsertRelativeYearOffset, "Month Key", each [Year] * 100 + [Month of Year]),
 InsertQuarterKey = Table.AddColumn(InsertMonthKey, "Quarter Key", each [Year] * 10 + [Quarter of Year]),

A personal preference of mine is to use the Feb 2018 formatting for the “Month” attribute and Q1 2018 for the “Quarter” attribute (see “InsertCalendarMonth” and “InsertCalendarQtr” steps above). Doing this means I need another field I can use to sort by, because the alphanumeric sorting is not guaranteed to be correct. The two steps above give me an integer representation for that purpose.

 InsertCrossJoinID = Table.AddColumn(InsertQuarterKey, "CrossJoin ID", each 1)

This may seem odd to you, but I’ve had occasions where I had to identify gaps in data or even generate data for missing source records. A hard-coded “1” allows me to do a cross-join between entities, so I have included that in my standard template. (This will probably be a good topic for another blog post)

Step 2: Relative date attributes

 InsertRelativeDateOffset = Table.AddColumn(InsertWeekEnding, "Relative Date Offset", each([Date] - Date.From(DateTime.LocalNow()))),
 InsertRelativeWeekOffset = Table.AddColumn(InsertRelativeDateOffset, "Relative Week Offset", each (Number.From(Date.StartOfWeek([Date], Day.Saturday)) - Number.From(Date.StartOfWeek(DateTime.LocalNow(), Day.Saturday))) / 7),
 InsertRelativeMonthOffset = Table.AddColumn(InsertRelativeWeekOffset, "Relative Month Offset", each (((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(DateTime.LocalNow())) + Date.Month(DateTime.LocalNow())))),
 InsertRelativeYearOffset = Table.AddColumn(InsertRelativeMonthOffset, "Relative Year Offset", each(Date.Year([Date])) - Date.Year(DateTime.LocalNow())),

Ever wanted to create a report, dashboard or visualization that always shows the current month or year, or the last 6 months? Adding relative dates allow you to do just that, and the first part is to create integer attributes with the offset (code snippet above) in your date function (see Chris Webb’s original blog post on date offsets here). Similar to Chris’s post, I also use DAX to generate more user friendly attributes. The SWITCH function works well for me here:

Relative Date = 
SWITCH
( 
'Date'[Relative Date Offset]
, -1
, "Yesterday"
, 0
, "Today"
, 1
, "Tomorrow"
, "Today "  &  IF ( 'Date'[Relative Date Offset] > 0,"+ ","- " )  &  VALUE(ABS('Date'[Relative Date Offset]))
)

Using the offsets of the other date-related attributes, you can easily create the same for week, month and year.

Step 3: Dynamic start & end dates

Invoking the date function requires a start & end date, but we don’t always know ahead of time what that will be. We also don’t want to have dates show up in our reports that cover periods for which we don’t have data, and to achieve that we have to dynamically assign values to the start & end date parameters.

My typical approach is to use another source query’s date values (min and max) when invoking the function, and the Power Query code will look something like this:

let
 Source = #"Date Function"
 (
 Record.Field(Table.Min(#"Timesheet","Date"), "Date")
 , Record.Field(Table.Max(#"Timesheet","Date"), "Date") 
 , null
 )
in
 #"Date Function"

In the code snippet above I am using the “Date” attribute from the “Timesheet” query and returning the min/max values for the start/end date parameters of the function. This is a simplified example, but with a little research you’d be able to use different attributes from different source queries, or even create conditional logic to determine where you’d like to start and end the values of your date entity.

Want to see the date function in action? Download my Power BI Date dimension template.