14- Introduction to Time Intelligence

At Last, It is Time!

(Get it? Time? There is no extra charge for humor of this quality).

I’ve been eagerly awaiting this chapter. PowerPivot measures really shine when you use them to perform intelligent calculations against the calendar.

It is a simple matter to perform calculations that answer questions like the following:

• How is our business performing relative to the same time last year?

• What were our Year to Date (YTD) sales as of June 1st?

• What was our best quarter over the past two years?

That is merely scratching the surface though. Good stuff. But before we dig in, a quick note about different types of calendars.

“Standard Calendar” versus “Custom Calendar”

Standard Calendars: The focus of This Chapter

Right up front, I want to let you know that this chapter will be written strictly from the perspective of the standard calendar.

What do I mean by “standard calendar?” It’s the calendar with the following properties:

• February has 28 days (29 in leap years) in it, and all other months have 30 or 31 days in them

• Quarters consist of three consecutive months – months whose lengths are described above

• Years have 365 days in them (366 in leap years)

• A given month this year might have more/less Saturdays (or any other day) in it than the same month last year

In other words, a standard calendar is the calendar that you have hanging on your wall.

PowerPivot’s time intelligence functions operate under the assumption that you use a standard calendar. So they represent a natural place to start the topic of time intelligence.

Custom Calendars: Perhaps Even More Important than Standard (Covered Later)

But many businesses do not measure themselves via the standard calendar. The standard calendar poses many problems that are often unacceptable:

• Comparing this month to last month is often not “fair” when last month had 31 days and this one has 30, for instance. Did we really perform 3% worse this month or is that just due to the different number of days?

• Even two months of the same length are often not fair comparisons since they contain different numbers of weekend days versus weekdays.

• Sometimes the unit of time measured doesn’t even resemble the wall calendar – “Semesters” in the academic world and “Seasons” in the sports world for example

• Going further, sometimes (such as in science), we want to literally compare time periods instead of calendar periods – such as “the first five minutes after an event” compared to the following fifteen minutes etc.

In my experience, at least half of all organizations measure themselves by custom calendars. Retail businesses in particular are very sensitive to those first two problems.

So have no fear, we will address custom calendars too. We are only going to start with the standard calendar. Stay tuned, in later chapters, for the custom calendar treatment.

Calendar: A Very Special Lookup Table

Everything in time intelligence requires that you have a separate Calendar table. (It does not have to be named “Calendar,” but I usually use that name, or “Dates.”)

Where to Get a Calendar Table

There are many ways to create a calendar table. Here are a few options:

• Import one from a database. This is my favorite, for several reasons. But it’s not an option for everyone.

• Create one in Excel. Pretty much available to everyone, but it does pose problems such as the need to adjust it every day.

• Import one from Azure DataMarket (or elsewhere on the internet). There’s at least one calendar table available for free download on the internet, produced by the amazing Boyan Penev. See
http://ppvt.pro/UltDate for more on this.

Properties of a Calendar Table

A calendar table must:

• Contain at least one column of “date” data type.

• Contain exactly one row per day.

• Contain completely consecutive dates, no gaps – even if your business is never open on weekends, those days must be in the calendar

• Be related to all of your Data tables (Sales, etc.)

• Contain columns for all of your desired grouping and labels – things like MonthName, DayOfWeekName, IsWknd, IsHoliday, etc. (strictly, you can have a Calendar table with just the one date column, but the Calendar table is the place to put all of these other columns if you do have them).

• Ideally only “spans” the relevant date ranges for your purposes. If your business opened in 2001, it doesn’t make sense for your Calendar table to start in 2000. And if today is June 20, 2012, it doesn’t make sense for June 21, 2012 to be in the Calendar yet. This is one of the trickier requirements – it’s the primary reason why I like to source my Calendar from a database. It really is optional, but you will find it very useful over time. Don’t worry about it much for now.

My Calendar table: imported and related

Figure 180 Calendar table – now we can get started!

Now I relate it to my Sales table, using the Date columns:

Figure 181 Relating Calendar to Sales

In PowerPivot v1, the column used to relate Calendar to other tables had to be of data type Date. In v2, you can now relate using a column of a different data type, such as an integer, so you do not need a column of Type Date in your Sales table anymore, but you do still need a column of type Date in your Calendar table.

Figure 182 Updated diagram view: Calendar becomes the third lookup table

Operates Like a Normal Lookup Table

Figure 183 [Total Sales] with Calendar[DayNameOfWeek] on Rows

And the Sort By Column feature works here too of course:

Figure 184 Sort by Column Rides Again!

Figure 185 Days sorting in proper order (if you want Monday to be first, just create a calculated column in Calendar that starts with 1 for Monday and ends on 7 for Sunday, and use that as your sort by column instead)

And we can repeat the same process for MonthName – every column can have its own separate sort by column:

Figure 186 Setting sort order for MonthName

Figure 187 Properly sorted month names!

First Special Feature: Enable Date Filtering via Mark as Date Table

With your Calendar table active, go to the Design tab of the ribbon and select Mark as Date Table:

Figure 188 Make this a habit for your Calendar/Date table

Then, in the pivot, you get the special date filtering options:

Figure 189 PowerPivot “tells” Excel that this is a Date table, so Excel enables these filter options for you (most of which are useless with my sample data since the dates are ancient, but more useful in the real world)

If you are going to use a column of non-Date data type to relate your Calendar table to your Data tables, you MUST “mark it as date” in the PowerPivot window, or many other of the smart calculation features covered after this will not function properly.

Second Special Feature: Time Intelligence Functions!

PowerPivot includes many new functions relating to time:

Figure 190 A subset of the DAX functions relating to time – a few are carryovers from normal Excel, but most are new.

Diving in with DATESYTD()

There are so many functions that it was hard for me to choose which one to cover first. I picked DATESYTD() not because it’s somehow special relative to the others, but just because it makes for a good example.

Let’s start with a simple pivot:

Figure 191 Our “testbed” for DATESYTD()

Now let’s add a new measure, one that tracks Year to Date (YTD) sales:

[Total Sales YTD] =

=CALCULATE([Total Sales], DATESYTD(Calendar[Date]))

And…

Figure 192 New measure shows us a running total of YTD sales for each month!

And like all good PowerPivot measures, this formula is “portable” into basically any report shape you desire, just by rearranging the pivot – no formula surgery required! Remove [Total Sales] and drag Year to Columns…

Figure 193 Our new [Total Sales YTD] measure, like all good DAX measures, automatically adjusts to any new pivot shape – just rearrange using the field list, and the measure does the hard work!

Anatomy of DATESYTD()

Function Definition

DATESYTD(<date column in calendar table>, <optional year end date>)

That first argument, <date column in calendar table>, is common to nearly all of the time intelligence functions. In PowerPivot itself, the function help just refers to it as Dates:

Figure 194 What I call “<date column in calendar table>, PowerPivot calls “Dates” – whenever you see that, remember my version of it, because that’s what “Dates” means in the time intelligence function definitions.

DATESYTD() is used as a <filter> argument to CALCULATE(), much like ALL() and FILTER().

How Does it Work?

Like almost everything else “magical” in PowerPivot, DATESYTD() operates by manipulating filter context.

Let’s return to a simple pivot layout, and highlight a particular measure cell:

Figure 195 For the highlighted measure cell…

DATESYTD() essentially identifies the latest date in the current filter context, and then “expands” the filter context backward from that date to the first date of the year (more specifically, to the first date in the year of that previously-identified latest date, which is 2004 in this case).

OK, then DATESYTD() modifies that filter context. Here’s how.

Figure 196 If we imagine the Calendar table as a calendar rather than a table, where each row in Calendar is a single date, these are the active dates (rows) in the filter context for the measure cell highlighted in the prior figure.

Again, visualizing the Calendar table in calendar form:

Figure 197 DATESYTD() starts at the last date in the existing filter context, and then “expands” the filter context back to the first date of the year (the first date in the year of the current filter context)

Resulting in a new filter context:

Figure 198 New filter context highlighted (again visualizing the Calendar table as a calendar)

Changing the Year End Date

That last argument to DATESYTD(), which is an optional argument that I left blank in the first example, allows you to customize your calendar just a little bit. That allows you to treat June 30 as the last day of the year, for instance, which is common in Fiscal Calendars.

Here’s a measure that does just that:

[Total Sales Fiscal YTD] =

CALCULATE([Total Sales], DATESYTD(Calendar[Date],"6/30/2004"))

Now let’s compare that to the original YTD measure, side by side. I’ve added Calendar[WeekNumOfYear] to Rows, nested under Month:

Figure 199 Original YTD measure starts from 0 in January, but Fiscal YTD version already is approaching $3M.

Note how I have sliced the pivot to 2003 even though I specified 6/30/2004 in the measure. The year itself does not matter in that last argument – the DATESYTD() function only looks at month and day and ignores the year (in that particular argument.)

Now let’s scroll down and see what happens at the end of June:

Figure 200 Fiscal YTD measure resets at the end of June, just as desired

So the built-in time intelligence functions are capable of adapting to different year end dates. This still falls under what I call the Standard Calendar however, because the months are all still the same as the months on the wall calendar – June still has 30 days, July has 31, etc. Only when we start redefining our notions of Month/Quarter/Year to be a different from the wall calendar do we start to “break” functions like DATESYTD(). You will see what I mean when we get to that chapter.

DATESMTD() and DATESQTD() – “Cousins” of DATESYTD()

These functions are the “month to date” and “quarter to date” versions of DATESYTD(), so I won’t walk you through them – their usage is just like what I’ve illustrated for DATESYTD(). The only difference is that neither of them offer that optional second argument for YearEnd Date.

TOTALYTD() – Another Cousin of DATESYTD()

TOTALYTD() is actually a replacement for CALCULATE(), one that “bakes in” a DATESYTD().

For example, our original YTD measure:

[Total Sales YTD] =

=CALCULATE([Total Sales], DATESYTD(Calendar[Date]))

Can be rewritten as:

[Total Sales YTD] =

=TOTALYTD([Total Sales], Calendar[Date])

I suppose that’s a bit more readable – shorter for sure. But I don’t see this as particularly necessary, we’d be fine without this function. Whether you choose to use it is really just a matter of personal preference.

The Remaining (Many) Time Intelligence Functions – Grouped Into “Families”

As I said previously, there are many time intelligence functions. But it’s pretty easy to group them into “families” (to continue the “cousin” metaphor). If I cover an example from each family, that will give you a foundation – the ability to quickly adopt whatever function you need – without me boring us both to death covering every single function.

We’ve already covered the DATESYTD() family. Let’s press forward, and take a tour of each remaining family.

FIRSTDATE() and LASTDATE()

This is a simple family, and it only contains these two.

Quite simply, these are the date versions of MIN() and MAX()

Briefly, let’s define two measures:

[FIRSTDATE Example] =

FIRSTDATE(Calendar[Date])

And:

[LASTDATE Example] =

LASTDATE(Calendar[Date])

And look at them on our Month/Weeknum pivot:

Figure 201 FIRSTDATE() and LASTDATE() in action

In the field list I placed both of these measures on the Calendar table since their “arithmetic” operates on the Calendar itself – they return dates rather than sales data or product counts, etc.

ENDOFMONTH(), STARTOFYEAR(), etc.

These return single dates, and have special handling for different “size” periods of time.

Again, let’s illustrate by example:

[ENDOFMONTH Measure] =

ENDOFMONTH(Calendar[Date])

Figure 202 Does about what you expect right?

Now let’s swap out Month for Quarter on Rows:

Figure 203 9/30/2001 is the last date in the last month of Q3 2001

Make sense? If you feed more than a single month to ENDOFMONTH(), it will find the last date in the last month.

But when you feed it a filter context of “size” less than a month, we get something different:

Figure 204 ENDOFMONTH() returns the last day of the month even if that day is NOT part of the current filter context.

The rest of this family behaves in much the same way.

CLOSINGBALANCEMONTH(),CLOSINGBALANCEYEAR(), ETC.

These functions are CALCULATE() replacements that have “hardwired” date logic equivalent to ENDOFMONTH(), STARTOFYEAR(), etc.

CLOSINGBALANCEMONTH(<measure expression>,<Date Column>,<optional filter>)

Example measure:

[Total Sales CLOSINGBALANCEMONTH]=

CLOSINGBALANCEMONTH([Total Sales], Calendar[Date])

Figure 205 CLOSINGBALANCEMONTH() always returns the value of its base measure on the last day of the month in the current filter context. (I have used a Sales measure here to demonstrate, but in reality, these functions are more useful with things like Inventory or Cash Balance.)

DATEADD() and SAMEPERIODLASTYEAR()

DATEADD()

This function is also used as a <filter> argument to CALCULATE(), and shifts your date filter context forward or backward in time.

DATEADD(<Date Column>, <number of intervals>, <interval type>)

• <Date Column> - the usual. Put your date column from your calendar table here.

• <Number of Intervals> - Set this to 1 to move one interval later in time, -1 to move back one, etc.

• <Interval Type> - Set this to Year, Quarter, Month, or Day – no quotes

Example measure that shows us last year’s [Total Sales]:

[Total Sales DATEADD 1 Year Back] =

CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, Year))

Here are its results for 2003 side-by-side with a pivot showing the original [Total Sales] measure for 2002:

Figure 206 DATEADD() version filtered to 2003 matches the original measure filtered to 2002

And now the same comparison, but with Quarter on Rows instead:

Figure 207 Same comparison, just with Quarter on Rows rather than Month. Again, perfect match.

Growth Versus Last Year (Year-Over-Year, YOY, etc.)

One obvious application of DATEADD() and similar functions is the calculation of growth versus the prior year.

[Pct Sales Growth YOY] =

([Total Sales] – [Total Sales DATEADD 1 Year Back]) /
[Total Sales DATEADD 1 Year Back]

Figure 208 [Pct Growth YOY] displayed for 2003 and compared to 2002 in the second pivot

Quirks and Caveats

There are a few things you will discover about DATEADD() that might make you scratch your head a bit, so I’ll give some advanced notice.

You must have contiguous date ranges on your pivot

If I filter a Quarter out of my pivot I will get an error:

Figure 209 Filtering Quarter 3 out of the pivot…

Figure 210 …yields an error with DATEADD()

The same thing would happen if I were using Month on rows and filtered out one or more months.

Note that the error occurs in the Grand Total cell. There is nothing wrong with each of the single-Quarter measure cell calculations, but when the Grand Total fails, the entire pivot fails. The filter context of the Grand Total cell is Quarter={1,2,3} and Year={2003}, and when DATEADD() goes back a year, that “skips” Quarter 4 of 2002, which DATEADD() cannot do.

Merely hiding the Grand Total (using the pivot Design tab on the ribbon) will not fix this problem. The only way to fix this is to prevent the Grand Total from even being calculated in the first place, which I will explain in the chapter on IF().

DATEADD() has special handling for “complete” months/quarters/years

This one and the next one are really subtle. If you struggle to understand, don’t worry about it – just remember that there’s something special going on here, so that if/when you discover this on your own, you can come back here and re-read this section.

2004 is a leap year, in which February contains 29 days. Let’s add a simple measure to the Calendar table that shows this:

[Number of Days] =

COUNTROWS(Calendar)

Figure 211 29 days in Feb 2004

And now I will add the DATEADD() measure we created before, [Total Sales DATEADD 1 Year Back]:

Figure 212 Question: does $489,090 represent 28 days of 2003 sales, or 29 days?

Let’s compare that to a 2003 pivot for the “raw” [Total Sales] measure:

Figure 213 DATEADD() is returning 28 days’ worth of Feb 2003 sales even though it starts out with a 29-day filter context in 2004!

DATEADD() Lacks intelligence for Weeks

Figure 214 With WeekNum on Rows, the DATEADD() measure does NOT match!

To see why the numbers don’t match, we need to add Date to Rows as well:

Figure 215 Both pivots report Sunday through Monday, but the DATEADD() measure is returning 2003’s Sunday sales in the context of 2004 Monday

Stated another way, the weeks are misaligned by one day:

Figure 216 Why doesn’t this work, if it works for Month and Quarter? Well for starters, 52 weeks in a year times 7 days per week = 364. So we are never going to get weeks quite right unless we change years to be 364 days long instead of 365 (which some custom calendars actually do).

So the concept of “week” is defined only in my Calendar table, in the WeekNumOfYear column. Look at the pivots above – Week 1 of 2004 has only 3 days in it! And Week 1 of 2003 has only 4!

That’s purely the “fault” of my Calendar table:

Figure 217 My Calendar table DOES only have 3 days in it for Week 1 of 2004

Whereas the time intelligence functions can intrinsically “know” what we mean by Month/Quarter/Year, they rely on the calendar table for all other concepts, so there isn’t any “magic fixup” when I navigate using DATEADD() in a filter context involving weeks.

SAMEPERIODLASTYEAR()

SAMEPERIODLASTYEAR(<Date Column>)

This is a shortcut function that is just a wrapper to DATEADD(). It is 100% equivalent to DATEADD() with “-1, Year” as the last two arguments:

SAMEPERIODLASTYEAR(Calendar[Date])

Is exactly the same as:

DATEADD(Calendar[Date], -1, Year)

PARALLELPERIOD(), NEXTMONTH(), PREVIOUS YEAR(), etc.

PARALLELPERIOD()

This one is almost a wrapper to DATEADD(), but it differs in one crucial way that is best shown by example.

PARALLELPERIOD(<Date Column>, <number of intervals>, <interval type>)

Let’s create an example measure:

[Total Sales PARALLELPERIOD Back 1 Year] =

CALCULATE([Total Sales], PARALLELPERIOD(Calendar[Date], -1, Year))

Figure 218 PARALLELPERIOD() always fetches the full year when you go back 1 year, no matter what “size” your filter context is (Month in this case).

So PARALLELPERIOD() navigates just like DATEADD(), but when it gets to its “destination,” it expands the filter context to the size of the specified <interval type> - Year, Quarter, or Month.

Reminder: you don’t have to remember all of the details of all of these functions. (I sure don’t!) You just need to know that they exist, then be able to find the one that serves your current purpose, and quickly re-familiarize yourself as needed.

NEXTMONTH(), PREVIOUSYEAR(), etc.

These functions are all just wrappers to PARALLELPERIOD() – they navigate and expand in exactly the same way.

[Total Sales NEXTMONTH]=

CALCULATE([Total Sales], NEXTMONTH(Calendar[Date]))

Figure 219 NEXTMONTH() always grabs the FULL next month, even if we start in the context of a single day.

DATESBETWEEN()

Ah, I have a special place in my heart for DATESBETWEEN(). Sometimes, you don’t want anything special – you just want total control over the date range in a measure. And DATESBETWEEN() delivers just that.

DATESBETWEEN(<date column>, <start date expr>, <end date expr>)

Let’s start with a very simple example:

[Total Sales First Half 2003] =

CALCULATE([Total Sales],
DATESBETWEEN(Calendar[Date], "1/1/2003","6/30/2003")
)

Figure 220 Note how DATEBETWEEN() completely overrides existing filter context on the Calendar table, otherwise it would be blank for July-December (and for January-June would match [Total Sales] for each month)

“Life to Date” Calculations

Earlier, we used DATESYTD() to calculate “year to date” sales, but what if you want a running total that does not reset at the start of each year, but instead just keeps piling up year after year?

Fortunately, DATEBETWEEN() lets us use expressions for the endpoint arguments:

[Total Sales Life to Date] =

CALCULATE([Total Sales],
DATESBETWEEN(Calendar[Date], "1/1/1900",
LASTDATE(Calendar[Date])
)
)

Figure 221 “Life to Date” using DATESBETWEEN() matches grand total across 2001-2003, as expected

Figure 222 Expanding to Month level, “Life to Date” measure still returns expected results

Removing That Hardwired 1/1/1900

Yeah, that’s ugly. Let’s replace it with FIRSTDATE(ALL(Calendar[Date])):

[Total Sales Life to Date] =

CALCULATE([Total Sales],
DATESBETWEEN(Calendar[Date],
FIRSTDATE(ALL(Calendar[Date])),
LASTDATE(Calendar[Date])
)
)

Why ALL(Calendar[Date])? Because otherwise we’d just get the first date in the filter context, (which would be January 1, 2003 in the $10,235,582 cell highlighted in the pivot above). We need to apply ALL() in order to clear the current filter context and literally find the first date in the entire Calendar table.

Note that we do not want ALL() on the LASTDATE() in the <end date> argument of DATEBETWEEN() in this case, otherwise it would always return sales for all time, and not sales up until the current filter context date.

DATESBETWEEN() is Fantastic with Disconnected Tables too!

You remember the Min/Max Threshold version of disconnected tables? You can do the same thing with dates, using a disconnected DateRange table, your normal Calendar table, and DATESBETWEEN().

I won’t belabor that here, since it’s a repetition of a familiar pattern, but for a detailed example, see
http://ppvt.pro/ABCampaign