Microsoft Office Tutorials and References
In Depth Information
Chapter 7
Date Calculations in DAX
Chapter 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Date Calculations in DAX................................203
Many analyses of data have to deal with dates. Microsoft SQL Server PowerPivot for Excel
offers a number of functions that simplify many calculations on dates that are typical in a
business scenario, but using the right function in the right way requires some explanation.
As you see in this chapter, the first step in date calculations is to create a separate Dates
table that supports most of the requirements.
Working with a Dates Table
In some examples in the previous chapters, we defined calculated columns that extracted parts
of the date that we used to group dates, such as the year and the month. This technique might
be applied to each table containing a date, but it would quickly become hard to manage. It
is better to create a separate table containing a row for each date, using the date as a key to
link that Dates table with other tables that contain data related to a date. In this way, you
obtain a model wherein all attributes about dates are included in a separate table and are
easy to access when you browse data with a PivotTable, as you can see in Figure 7-1.
FIguRE 7-1 PivotTable browsing Order data by using a Dates table named OrderDate.
A Dates table is also useful for making calculations using special DAX functions that operate
on Dates. These functions, of which DATEADD is an example, often require that all the days
in a given range exist in the data table—otherwise, a missing day might result in a wrong
Search JabSto ::

Custom Search