Microsoft Excel 2013: Building Data Models with PowerPivot

Chapter 12. Performing date calculations in DAX

Many analyses of data involve dates. In fact, most, if not all, business reports require calculations like Year to Date, Month to Date, Same Period Last Year, and similar computations. These kinds of calculations are often called time intelligence. Microsoft PowerPivot for Excel offers a number of functions that simplify many calculations on dates that are typical in a business scenario. In this chapter, you are going to learn the techniques needed to handle time intelligence, and in the meantime, learn some DAX techniques that will prove useful in different scenarios.

Building a calendar table

To work with time intelligence, you need to have a calendar table inside the data model. Sometimes the data ...