Hello Digital Friends,

It’s been awhile since my last post so I wanted to talk about a must for any data model you are creating. My number one rule for any data model with dates is to create a separate date table. It is very simple to do and it will increase the flexibility of your modeling and make your life much easier when it comes to creating nice visualizations with date ranges.

If you are not familiar with working with a date table, here’s a quick guide to get you started:

  1. The date table must have a row for each individual day. This is the most important rule to follow. You must not skip any days within the date range you are creating for your date table.
  2. What should the date range of your date table be? A good rule is to always make sure you go far enough in the past to cover any dates that your fact tables may contain. I also always like to go into the future a few years. This way you do not have to constantly remember to add future dates into your date table.
  3. Power BI and other data visualization software make it extremely easy to add date tables manually or script them in.
  4. Once you have your custom date table you can add many detail summary columns to the rows like month, year, quarter, fiscal year, etc. You can even add dynamic columns that will flag whether a date is in the past based off the current date and time. You can use this to filter out future dates in your reports.
  5. Connect the date table to your fact tables with the day date field in your calendar table. This should create a one to many or a one to one relationship with your fact table. This is because the primary key of the date table is the date column. The granularity of the date table is at the day level. If you need hour level detail you can add a separate time table (which will be a future post of mine!)

Here is a common date table script that I have used in many of my Power BI reports. You specify your desired date range in the first function Duration.Days below and then the rest is Power Query magic. You just select get data from the Power BI menu and open up the advanced editor on the top ribbon and past the below script in.

let	
	  DayCount = Duration.Days(Duration.From(Date.FromText("01-01-2026") - Date.FromText("01-01-2010"))),
	  Source = List.Dates(Date.FromText("01-01-2010"),DayCount,#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]), type number),
	  InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), type number),
	  InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text),
	  InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], type number),
	  InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), type number),
	  InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM", Culture.Current), type text),
	  InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM", Culture.Current), type text),
	  InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
	  InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], type number),
	  InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), type number),
	  InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),
	  InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], type number),
	  InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Week Ending", each Date.EndOfWeek([Date]), type date),
	  InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), type number),
	  InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], type number),
	  InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, type number),
	  InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd", Culture.Current), type text),
	  InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 1 then "Y" else if [Day Num Week] = 7 then "Y" else "N", type text),
	  InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd", Culture.Current), type text),
	  InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1),
	  InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), type number),
	  InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),
	  InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),
	  InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),
	  InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),
	  InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),
	  InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then "Y" else "N", type text),
	  InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then "Y" else "N", type text),
	  InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then "Y" else "N", type text),
	  InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then "Y" else "N", type text),
	  InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else "N", type text),
	  #"Changed column type" = Table.TransformColumnTypes(InsertCompletedYear, {{"Year", Int64.Type}, {"Quarter Num", Int64.Type}, {"Quarter Year Order", Int64.Type}, {"Month Num", Int64.Type}, {"Month Year Order", Int64.Type}, {"Week Num", Int64.Type}, {"Week Year Order", Int64.Type}, {"Month Day Num", Int64.Type}, {"Day Num Week", Int64.Type}, {"Date Int", Int64.Type}, {"Index", Int64.Type}, {"Day of Year", Int64.Type}})
	in
	  #"Changed column type"

What this M script gives you is a great date table to start with. There are many different descriptive columns to work with and you can add your own to this table as you need.

I’ll be updating this post with an example Power BI file with the date table connected to my sample “playground” pharmacy data set.

Drop any questions and comments below. As always, Stay digital!

Ben

Leave a Reply

Processing...
Thank you! Your subscription has been confirmed. You'll hear from us soon.
Subscribe For More Content
Digital Pharmacist
ErrorHere