57.1 DAX Formulas for PowerPivot – Chapter 14

Chapter 14 is about time intelligence. If I were Rob and this were a blog post (actually it is a blog post), I might start off something like this:

Ah, Time Intelligence, the cornerstone of every nutritious breakfast...

Time Intelligence. The cornerstone of every nutritious breakfast..

Of course, I’m not Rob so I won’t be doing that. But the point is that time intelligence is fundamental to business intelligence. And chapter 14 (a comparatively large one for this book) introduces time intelligence in DAX.

Time intelligence allows you to analyze data comparatively on various dimensions and time is an important one. Year to date metrics, moving averages, metrics compared to the same period last year, and things like that all use time intelligence functions in their calculations. The chapter starts off by making the point that the time intelligence functions in DAX work with “standard calendar” – the calendar most of us are familiar with. It makes that point that many business scenarios involve non-standard calendars and those will be covered but not in this chapter. It then continues describing how to build a date lookup table and how to configure it so that Excel and the DAX time intelligence functions can use it. Then the various types of time intelligence functions are described.

This is an important chapter. It’s hard to imagine becoming a “DAX master” without having knowledge of how to use time intelligence functions in your “tool belt.”

BTW, on page 130 it looks like there is something missing:

“Let’s create an example measure:
[Total Sales PARALLELPERIOD Back 1 Year] =”

and then no formula for the measure. If you look in the book’s companion files (specifically ch14.xlsx), you can find it in the model:

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s