Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

The Importance of a 'Calendar' or 'Date' Table with samples in M and Dax

By Ashley Hsia posted Jun 26, 2020 07:03 PM

  
If you are just getting started with Power BI you may be seeing a lot of references to a 'Calendar' Table or 'Date Table.  Maybe you already have a handle on this aspect of PBI, but a lot of people seem not to understand how to get started.  If you forego having a 'Calendar' Table altogether, you can end up having a very cumbersome file and you won't be able to take advantage of many of the awesome date/time functions offered through Power BI.  There are quite a few good resources already out there.  This post points you to a few of those and helps you get started in both M and DAX.

To turn off Time intelligence - here is a great blog resource: sqlbi - DAX 101: Automatic time intelligence in Power BI

You want your date table to encompass all of the dates you will be using (so if you have a lost of historic data, you will need your calendar to cover the earliest dates you are utilizing).  

Guy in a Cube has a great video to get started with a DAX calendar.  Here is some DAX based on that:

Dates =

VAR BaseCalendar =

CALENDARAUTO (6)

RETURN

GENERATE (

BaseCalendar,

VAR BaseDate = [Date]

VAR YearDate = YEAR ( BaseDate)

VAR MonthNumber = MONTH ( BaseDate)

RETURN ROW (

"Day", BaseDate,

"Year", YearDate,

"Month Number", MonthNumber,

"Month", FORMAT ( BaseDate, "mmmm"),

"Year Month", FORMAT (BaseDate, "mmm yy"),

"Quarter", "Q"&FORMAT (BaseDate, "q")

)

)


If you would like something a little more manual and spelled out, this is a good resource: ArcherPoint - Creating A Date Table In Power BI

If you want to get started with a Calendar Table in Power Query (M), here are two samples you can test out:

1. This one looks at the dates in a Sales table that has opportunities listed out in the future:

let

   StartDate = List.Min(Table.Column(Sales,"ContractDate")),

   EndDate = List.Max(Table.Column(Sales,"ContractDate")),

   DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

   Source = List.Dates(StartDate,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"}}),

   AddYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),

   AddQuarter = Table.AddColumn(AddYear, "Quarter", each Date.QuarterOfYear([Date]), type number),

   AddCY = Table.AddColumn(AddQuarter, "CY", each if Date.IsInCurrentYear([Date]) then 1 else 0),

   AddCQ = Table.AddColumn(AddCY, "CQ", each if Date.IsInCurrentQuarter([Date]) then 1 else 0),

   ThreeYearsPrior = Table.AddColumn(AddCQ,"3 Year Lookback", each if Date.IsInPreviousNYears([Date],3) then 1 else 0),

   AddPY = Table.AddColumn(ThreeYearsPrior,"PY", each if Date.IsInPreviousYear([Date]) then 1 else 0),

   AddMonthNumber = Table.AddColumn(AddPY,"Month", each Date.Month([Date]), type number),

   AddMonthName = Table.AddColumn(AddMonthNumber,"Month Name", each Date.MonthName([Date]), type text),

   #"Changed Type" = Table.TransformColumnTypes(AddMonthName,{{"PY", Int64.Type}, {"3 Year Lookback", Int64.Type}, {"CQ", Int64.Type}, {"CY", Int64.Type}})

  

in

   #"Changed Type"


2. This one is based on data that has only populated out to the current day and we want the Calendar table to go to the end of the current calendar year:

let

   StartDate = List.Min(Table.Column(Finance,"Month")),

   EndDate = Date.EndOfYear(List.Max(Table.Column(Finance,"Month"))),

   DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

   Source = List.Dates(StartDate,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"}}),

   AddYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),

   AddQuarter = Table.AddColumn(AddYear, "Quarter", each Date.QuarterOfYear([Date]), type number),

   AddMonth = Table.AddColumn(AddQuarter, "Month", each Date.Month([Date]), type number),

   AddMonthName = Table.AddColumn(AddMonth,"Month Name", each Date.MonthName([Date]), type text),

   AddCY = Table.AddColumn(AddMonthName, "CY", each if Date.IsInCurrentYear([Date]) then 1 else 0),

   AddCQ = Table.AddColumn(AddCY, "CQ", each if Date.IsInCurrentQuarter([Date]) then 1 else 0)

in

   AddCQ


Here are some resources I used to eventually build my tables:
https://community.powerbi.com/t5/Desktop/Date-Dimension-Table-that-Dynamically-Pulls-Start-and-End-dates/td-p/76870
INSIGHTS & OUTLIERS - Need a Good Date Table for Power BI?
POWER BI TIPS AND TRICKS - Building Date Table from Scratch 

If you have some awesome tip, please share them in the comments.

1 comment
45 views

Permalink

Comments

Aug 14, 2020 04:53 AM

Hi Ashley,
Thanks for the great article.
I'm always wondering, adding a date calender in Power Query using M or in Data Modelling using DAX, which is better or there isn't much difference? I guess maybe from performance point of view, M is better? 
I've always use DAX cos M is much more scarier to me! 

Cheers. KB