Let’s just skip the part where I comment on how long it has been since my last post. You don’t care. I’ve been busy. I am updating an old post, cuz I’m crazy like that. Though, I didn’t actually read it before writing all the words below. Oh well, let’s do this!
What is a Calendar Table?
Wait, why does the blog post say Date Table, and here I said Calendar Table? Ya, I dunno either. I don’t think there has been in serious standardization on the term. Just roll with it.
Anyway, let’s say I’m lookin’ at some sweet data, and I want to be able to compare this quarter (or month, etc) to the same period… last year. ZOMG! There is actually a DAX function for that, called SAMEPERIODLASTYEAR! According to the docs, the syntax is
SAMEPERIODLASTYEAR(<dates>)
and MSDN says <dates> is “a column containing dates”. Well, okay, maybe my Sales tables already has dates, and I can just use that? SPOILER: Don’t do that.
There are bunch of “Time Intelligence” functions in DAX, and all of them take this <dates> column… and actually what you want is a separate date/calendar table. The main reason is that (while not super well documented) there are “rules” around this data column: it needs to cover the entire range of dates you care about without gaps. So, it needs to include holidays, weekends, etc. Every date.
What Belongs in a Date Table?
I really really want to say “whatever you want” and just jump to the next section. Cuz it would be (almost) funny. Almost.
So, realize your Date table is a “lookup table”. It’s not going to have a ton of rows, it’s probably going to be related to several fact/data tables (and by having the one dates table, you can filter by Year and have it filter ALL your fact tables by that Year), and adding helpful columns is useful and awesome: MonthName, MonthNumber, Quarter, Week#, DayName, etc. Whatever makes sense for your scenarios/reports. Only the actual date is required.
Likely, you will also want some columns that are just for sorting. Like, if you have a column of Qtr-Year (“Q1-2007”) … it is going to sort in a weird order unless you use the “Sort By” feature of Power BI / Power Pivot. So I would add a Qtr-Year-Sort that is [Year]*100 + [Qtr] so that it sorts in a reasonable way. See Reza’s super clear post here about this.
How do I create a date table?
Cinchy! Go to the modeling ribbon, click New Table (see picture at right), and use the magic formula “Dates = CALENDARAUTO( )”… done! This will scan all your tables for Date columns and create a calendar table that covers all the dates you need. You would then go add useful calculated columns (like Year, Qtr, etc). Though… honestly, I would search the interwebs… there are 5983 articles on how to create an awesome calendar table via 3525 techniques…. from PowerQuery/M scripts, to Excel workbooks, SQL queries, formulas for calculated columns, etc.
Note that there are other options instead of CALENDARAUTO() you can use CALENDAR( ) to explicitly set the date range. CALENDARAUTO( ) also takes a parameter for “End of Fiscal Year” date, in case that is useful to you.
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
Nice to have you back Scott 🙂
Nice – but when is the Add-in to create DAX measures with VBA coming out
Love me some interwebs.
I check your RSS feed every day when I’m going down my list, so it’s nice to see something from you! I’ve appreciated your useful content.