Microsoft PowerPivot for Excel 2010: Date Calculations in DAX

10/5/2010

Contents

Working with a Dates Table
Aggregating and Comparing over Time
Closing Balance over Time
Summary

Aggregating and Comparing over Time

Working days calculation is only the first step in the benefits that you can obtain by using a calendar table. In the next sections, we introduce other useful techniques. It is often required that you analyze particular aggregations of values over time. For example, you might want to calculate the aggregated value of a measure from the beginning of the year up to the period you are selecting. (This is commonly called year-to-date aggregation.) You might want to look at the Sales Amount for the month of March but also want to look at the total Sales Amount from January to March. Having a Dates table is an important prerequisite for making this calculation in PivotTable.

Year-to-Date, Quarter-to-Date, and Month-to-Date

The calculation of year-to-date (YTD), quarter-to-date, (QTD) and month-to-date (MTD) are all very similar. Obviously, month-to-date is meaningful only when you are looking at data at the day level, whereas year-to-date and quarter-to-date calculations are often used to look at data at the month level.

For example, in Figure 7-36, you can see the LineTotal measure aggregated by year, quarter, and month.

Figure 7-36 The LineTotal measure aggregated by the corresponding period in a row.

You can calculate the year-to-date value of LineTotal for each month and quarter by using a measure that operates on the filter context, modifying the filter context on dates for a range that starts on January 1 and ends on the month corresponding to the calculated cell. You can define a YtdLineTotal measure by using the following DAX formula:

YtdLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                          DATESYTD( OrderDate[Date] ) )

The CALCULATE function receives in its second parameter a table that contains the dates of the year-to-date period that has to be considered in the aggregation. This set of dates is returned by the built-in DATESYTD function, which is a Time Intelligence function that returns a list of all the dates from the beginning of the year until the last date included in the current filter context.

You can see the new measure in action in Figure 7-37.

Figure 7-37 The LineTotal year-to-date measure side-by-side with a regular measure.

This approach requires that you deal with the CALCULATE function, but because this pattern (using a CALCULATE and a DATESYTD function) is very common, PowerPivot offers a dedicated DAX function that simplifies (and makes more readable) the syntax of the YTD calculation, TOTALYTD:

YtdLineTotal = TOTALYTD( SUM( SalesOrderDetail[LineTotal] ),
                         OrderDate[Date] )

As you can see, the syntax requires the hoped-for aggregation as the first parameter and then just the date column as the second parameter. The behavior is identical to the original measure, but the name of the TOTALYTD function immediately communicates the intention of the formula. However, you need to know the behavior of the original CALCULATE syntax because it allows a more complex calculation that you define later in this chapter.

What Date Column to Use

Keep in mind that the date column that you must use when calling TOTALYTD (and other similar functions) is the date column of the Dates table, and not the date column of the table that is the object of analysis. In this case, the OrderDate[Date] column was used instead of the SalesOrderHeader[OrderDate] column. If we had used the latter, the calculation would have been wrong. You can see in Figure 7-38 the result that would have been produced by using the following formula for the YTD measure:

YtdLineTotal = TOTALYTD( SUM( SalesOrderDetail[LineTotal] ),
                         SalesOrderHeader[OrderDate] )

Figure 7-38 The wrong year-to-date calculation returns the same value as the LineTotal measure.

The problem is that the existing filter on year and month would be still applied. There are possible workarounds that you might use, but our suggestion is to always define and use a Dates table. Further details on the issue and possible workarounds are available in this blog post written by Kasper de Jonge: http://tinyurl.com/DaxTimeAll.

As you can for the year-to-date calculation, you can also define quarter-to-date and month-to-date calculations with built-in functions, as in these measures:

QtdLineTotal = TOTALQTD( SUM( SalesOrderDetail[LineTotal] ),
                         OrderDate[Date] )

MtdLineTotal = TOTALMTD( SUM( SalesOrderDetail[LineTotal] ),
                         OrderDate[Date] )

In Figure 7-39, you can see the year-to-date and quarter-to-date measures used in a PivotTable. Note that the quarter-to-date measure makes the year total equal to the last quarter of the year.

Figure 7-39 The year-to-date and quarter-to-date measures side-by-side with a regular measure.

To calculate a year-to-date measure over the fiscal year, you need to use an optional parameter that specifies the end day of the fiscal year. For example, you can calculate the fiscal year-to-date for LineTotal by using the following expression:

FiscalYtdLineTotal = TOTALYTD( SUM( SalesOrderDetail[LineTotal] ),
                               OrderDate[Date],
                               "06-30" )

The last parameter corresponds to June 30, which in our OrderDate table corresponds to the end of the fiscal year. You can find several Time Intelligence functions that have a last, optional YE_Date parameter for this purpose: STARTOFYEAR, ENDOFYEAR, PREVIOUSYEAR, NEXTYEAR, DATESYTD, TOTALYTD, OPENINGBALANCEYEAR, and CLOSINGBALANCEYEAR.

Periods from the Prior Year

People commonly need to get a value from a period of the prior year (PY). This can be useful for making comparisons of trends, during a period last year to the same period this year, as you can see in the CH07-08-Aggregation.xlsx workbook included on the companion DVD. This is the DAX expression you need to calculate that value:

PyLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                         SAMEPERIODLASTYEAR( OrderDate[Date] ) )

The CALCULATE function changes the filter by using the SAMEPERIODLASTYEAR function, which returns a set of dates shifted one year back in time. The SAMEPERIODLASTYEAR function is a specialized version of the more generic DATEADD function, which can be used by specifying the number and type of periods to shift. For example, the same PyLineTotal measure can be defined by this equivalent expression:

PyLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                         DATEADD( OrderDate[Date], -1, YEAR ) )

Sometimes you must look at the total amount of a measure for the previous year, usually to compare it with the year-to-date total. To do that, you can use the PARALLELPERIOD function, which is similar to DATEADD but returns the full period specified in the third parameter instead of the partial period returned by DATEADD. The PyTotLineTotal measure that calculates the total sum of LineTotal for the previous year can be defined this way:

PyTotLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                            PARALLELPERIOD( OrderDate[Date], -1, YEAR ) )

In Figure 7-40, you can see the result of the PyLineTotal and PyTotLineTotal measures. The quarters data in 2002 for the Sum Of LineTotal column has been copied into the respective quarters of year 2003 in the PyLineTotal column. The PyTotLineTotal simply reports for every period the total amount of the LineTotal column for the year before.

Figure 7-40 Prior Year simple calculations.

When you want to calculate the year-to-date of the prior year because, typically, you want to compare it with the current year-to-date measure, you have to mix the two techniques. Instead of passing the OrderDate[Date] parameter to SAMEPERIODLASTYEAR, which corresponds to the list of dates that are active in the current filter context, you can use the DATESYTD function to make a transformation of these dates, defining the year-to-date group first. However, you might also invert the order of these calls. In fact, the two following definitions of PyYtdLineTotal are equivalent:

PyYtdLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                            SAMEPERIODLASTYEAR( DATESYTD( OrderDate[Date] ) ) )

PyYtdLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                            DATESYTD( SAMEPERIODLASTYEAR( OrderDate[Date] ) ) )

You can see the results of the PyYtdLineTotal in Figure 7-41. The values of YtdLineTotal are reported for PyYtdLineTotal shifted by one year. In the same screen, you can also see the FiscalYtdLineTotal measure that you saw at the end of the previous section: the horizontal lines between Q2 and Q3 in that column highlight the points at which the year-to-date calculation restarts.

Figure 7-41 The year-to-date calculation for Prior Year and Fiscal Year.

Another commonly requested calculation that eliminates seasonal changes in sales is the moving annual total (MAT), which always considers the last 12 months. For example, the value of MatLineTotal for March 2002 is calculated by summing the range of dates from April 2001 to March 2002. Consider the following MatLineTotal measure definition, which calculates the moving annual total for LineTotal:

MatLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                          DATESBETWEEN(
                              OrderDate[Date],
                                  NEXTDAY(
                                      SAMEPERIODLASTYEAR(
                                          LASTDATE( OrderDate[Date] ) ) ),
                                  LASTDATE( OrderDate[Date] ) ) )

The implementation of this measure requires some attention. You need to use the DATESBETWEEN function, which returns the dates from a column included between two specified dates. Because this calculation is always made at the day level, even if the PivotTable is browsing data at the month level, you must calculate the first day and the last day of the interval you want. The last day can be obtained by calling the LASTDATE function, which returns the last date of a given column (always considering the current filter context). Starting from this date, you can get the first day of the interval by requesting the following day (by calling NEXTDAY) of the corresponding last date one year before. (You can do this by using SAMEPERIODLASTYEAR, as we did before.)

In Figure 7-42, you can see a PivotTable using the moving annual total calculation. For example, the 2003 Q2 data has been calculated by summing Q3 and Q4 of 2002, plus Q1 and Q2 of 2003. In the middle, you see the classic year-to-date calculation, which has the same value of moving annual total only for the last period of each year (in this case Q4).

Figure 7-42 The moving Annual Total vs. year-to-date calculation.

Other Aggregation Functions and the CALCULATE Syntax

In all the examples, we have used the SUM aggregation function. You might need to use other aggregation functions, such as AVERAGE, or more complex formulas. Whenever you saw SUM( SalesOrderDetail[LineTotal] ) in the previous example, consider that you can always replace such expressions with another DAX formula, also by simply replacing the aggregation function.

In case your calculation becomes more complex, you might prefer to specify that calculation, which can be shared across several other measures, into a separated measure, containing just this operation. In this way, you can avoid the duplication of the aggregation function in all the formulas for measures that make special calculations for dates.

For example, you might want to calculate the weighted average price with the following formula, assigned to measure AveragePrice:

AveragePrice = SUM( SalesOrderDetail[LineTotal] ) / SUM( SalesOrderDetail[OrderQty] )

You can use a direct reference to that measure, without using an aggregation function, whenever you use formulas such as CALCULATE or special Time Intelligence functions that behave like CALCULATE, as in the year-to-date calculation defined in YtdAveragePrice measure:

YtdAveragePrice = TOTALYTD( SalesOrderDetail[AveragePrice], OrderDate[Date] )

The calculation for the prior year can be written by using the CALCULATE function:

PyAveragePrice = CALCULATE( SalesOrderDetail[AveragePrice],
                            SAMEPERIODLASTYEAR( OrderDate[Date] ) )

In Figure 7-43, you can see the results of these formulas in a PivotTable.

Figure 7-43 The year-to-date and prior year calculations for Average Price.

In case you want to make a monthly average of the total sales, you should use the number of months in the denominator of the ratio, as in the following expression that you can use to define the MonthlyAverage measure:

MonthlyAverage = IF( COUNTROWS( VALUES( OrderDate[Month] ) ) > 0,
                     SUM( SalesOrderDetail[LineTotal] )
                         / COUNTROWS( VALUES( OrderDate[Month] ) ),
                     BLANK() )

Please note that this definition does not work with a selection of more than one year. To avoid this issue, you need to create a calculated column in the OrderDate table with a concatenation of year and month so that the distinct number of values over a period take account also of the year and not just the month. If you do that, you must replace that column to the Month column used in the formula above.

To make a monthly average of the year-to-date sales, you have to replace the corresponding YtdLineTotal measure at the numerator and you need a CALCULATE expression at the denominator so that you can calculate the number of months included in the year-to-date calculation:

YtdMonthlyAverage = IF( COUNTROWS( VALUES( OrderDate[Month] ) ) > 0,
                            SalesOrderDetail[YtdLineTotal]
                                / CALCULATE( COUNTROWS( VALUES( OrderDate[Month] ) ),
                                             DATESYTD( OrderDate[Date] ) ),
                        BLANK() )

As you can see, the expression might be different according to the calculation you have to do. You have to pay particular attention to the calculation necessary for numerators and denominators of any ratio and average measure.

Difference over Previous Year

A common operation that compares a measure with its value in the prior year is to calculate the difference of these values. That difference might be expressed as an absolute value or by using a percentage, as you can see in the CH07-09-Yoy.xlsx workbook included on the companion DVD. To make these calculations, you need the value for the prior year that you already defined in PyLineTotal:

PyLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                         SAMEPERIODLASTYEAR( OrderDate[Date] ) )

The absolute difference of LineTotal over previous year (year-over-year, YOY) is a simple subtraction. You can define a YoyLineTotal measure with the following expression:

YoyLineTotal = SUM( SalesOrderDetail[LineTotal] ) - SalesOrderDetail[PyLineTotal]

You calculate the value of the selected year by using the SUM aggregation; the measure corresponding to the value of the prior year does not need to be summed because the aggregation is already done as part of the underlying measure expression.

The analogous calculation for comparing the year-to-date measure with a corresponding value in the prior year is a simple subtraction of two measures, YtdLineTotal and PyYtdLineTotal, which you saw in the previous section; we report it here just as reminder:

PyYtdLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
                            SAMEPERIODLASTYEAR( DATESYTD( OrderDate[Date] ) ) )

YoyYtdLineTotal = SalesOrderDetail[YtdLineTotal] - SalesOrderDetail[PyYtdLineTotal]

Most of the time, the year-over-year difference is better expressed as a percentage in a report. You can define this calculation by dividing YoyLineTotal by the PyLineTotal; in this way, the difference uses the prior read value as a reference for the percentage difference (100 percent corresponds to a value that is doubled in one year). In the following expression that defines the YoyPercLineTotal measure, the IF statement avoids a divide-by-zero error in case there is no corresponding data in the prior year:

YoyPercLineTotal = IF( SalesOrderDetail[PyLineTotal] = 0,
                       BLANK(),
                       SalesOrderDetail[YoyLineTotal] / SalesOrderDetail[PyLineTotal] )

A similar calculation can be made to display the percentage difference of a year-over-year comparison for the year-to-date aggregation. You can define YoyPercYtdLineTotal by using the following formula:

YoyPercYtdLineTotal = IF( SalesOrderDetail[PyYtdLineTotal] = 0,
                          BLANK(),
                          SalesOrderDetail[YoYYtdLineTotal]
                              / SalesOrderDetail[PyYtdLineTotal] )

In Figure 7-44, you can see the results of these measures in a PivotTable.

Figure 7-44 The year-over-year (YOY) measures used in a PivotTable.

Simplifying Browsing with a Period Table

In this chapter, you have seen how to create single measures with special calculations over time, such as year-to-date, year-over-year, and so on. One drawback of this approach is that you have to define one measure for each of these calculations, and the list of the measures in your model might grow too long.

A possible solution to this issue, which is also an interesting generic modeling solution, is to create a special table containing one line for each of the calculations you might want to apply to a measure. In this way, the end user has a shorter list of measures and possible operations on them, instead of having the Cartesian product of these two sets. However, you can also see that this solution has its own drawbacks, and maybe it is better to create just the measures you really want to use in your model, trying to expose only the combinations of measures and calculations that are meaningful for the expected analysis of your data.

First of all, you create a Period table in Excel, which contains the list of possible calculations that should be applied to a measure, as you can see in Figure 7-45. The complete model used for this example is available in the CH07-10-PeriodTable.xlsx workbook included on the companion DVD.

Figure 7-45 A Period table in Excel.

The same table has to be imported as a linked table into PowerPivot. However, you do not have to define any relationships between this table and other tables in your model because you use the selected member of the Period table to change the behavior of a measure through its DAX definition. Nevertheless, PowerPivot warns you of a missing relationship when you browse data in a PivotTable, as you can see in Figure 7-46.

Figure 7-46 A warning about a missing relationship caused by the Period table.

This warning is provoked by the Period table, which does not have any relationships with other tables in the model. You can disable this warning by pressing the Detection button on the PowerPivot tab of the ribbon, which is the one highlighted in Figure 7-47.

Figure 7-47 The Detection button on the PowerPivot tab of the ribbon disables the detection of missing relationships.

At this point, you can define a single measure that checks the selected value of the Period table and uses a DAX expression to return the corresponding calculation. Because there are no relationships with the Period table, the selected value in the Period table is always the one chosen by the user whenever that table is used as a filter, or the selected value is the corresponding value in a row or a column whenever Period is used in Row or Column labels. In general, we follow this generic pattern:

= IF( COUNTROWS( VALUES( Period[Period] ) ) = 1,
      IF( VALUES( Period[Period] ) = "Current", <expression>,
      IF( VALUES( Period[Period] ) = "MTD", <expression>,
      ...

The first condition checks that there are not multiple values active in the filter context. In such a case, you should avoid any calculation because of the ambiguity of having multiple active values; otherwise, you should generate an error in the calculation, instead of returning a wrong value without warning the user. Then in the next step, each value is checked by a different IF statement, which evaluates the correct expression corresponding to the Period value. Assuming you have all the measures previously defined in this chapter, you need to replace the expression tag with the corresponding specific measure. For example, you can define a generic CalcLineTotal measure, which is used to apply one or more of the operations described in the Period table to the LineTotal measure:

CalcLineTotal = IF( COUNTROWS( VALUES( Period[Period] ) ) = 1,
      IF( VALUES( Period[Period] ) = "Current", SUM( SalesOrderDetail[LineTotal] ),
      IF( VALUES( Period[Period] ) = "MTD", SalesOrderDetail[MtdLineTotal],
      IF( VALUES( Period[Period] ) = "QTD", SalesOrderDetail[QtdLineTotal],
      IF( VALUES( Period[Period] ) = "YTD", SalesOrderDetail[YtdLineTotal],
      IF( VALUES( Period[Period] ) = "PriorYear", SalesOrderDetail[PyLineTotal],
      IF( VALUES( Period[Period] ) = "PriorYearMTD", SalesOrderDetail[PyMtdLineTotal],
      IF( VALUES( Period[Period] ) = "PriorYearQTD", SalesOrderDetail[PyQtdLineTotal],
      IF( VALUES( Period[Period] ) = "PriorYearYTD", SalesOrderDetail[PyYtdLineTotal],
      IF( VALUES( Period[Period] ) = "DiffPriorYear", SalesOrderDetail[YoyLineTotal],
      IF( VALUES( Period[Period] ) = "DiffPercPriorYear",
                                                      SalesOrderDetail[YoyPercLineTotal],
      IF( VALUES( Period[Period] ) = "DiffYTDPriorYear", SalesOrderDetail[YoyYtdLineTotal],
      IF( VALUES( Period[Period] ) = "DiffPercYTDPriorYear",
                                                      SalesOrderDetail[YoyPercYtdLineTotal],
  BLANK() ) ) ) ) ) ) ) ) ) ) ) ),
      BLANK() )

You have to repeat this definition for each of the measures to which you want to apply the Period calculations. You might avoid defining all the internal measures by replacing each reference to a measure with its corresponding DAX definition. This would make the CalcLineTotal definition longer and hard to maintain, but it is a design choice you might follow.

At this point, you can browse data by using the Period values crossed with the CalcLineTotal measure. In Figure 7-48, only the CalcLineTotal measure has been selected; the Period values are in the columns, and a selection of years and quarters is in the rows.

Figure 7-48 The Period calculations applied to the CalcLineTotal measure.

As we anticipated, this solution has several drawbacks.

After you put Period in rows or columns, you cannot change the order of its members. Actually, you can do this by using some Excel features, but it is not as immediate and intuitive as moving the list of measures into the Values list in the PowerPivot Field List panel.
The number format of the measure cannot change for particular calculations requested through some Period values. For example, in Figure 7-48, you can see that the DiffPercPriorYear and DiffPercYTDPriorYear calculations do not display the CalcLineTotal value as a percentage because you can define a single number format for a measure in a PivotTable. A possible workaround is to change the number format directly in Excel cells, but this change is lost as soon as you navigate into the PivotTable.
If you use more than one measure in the PivotTable, you must create a set based on column items in Excel, choosing only the combination of measures and Period values that you really want to see in the PivotTable. You can see an example of how to create these sets in the “Defining Sets” section of Chapter 8, “Mastering PivotTables.”
You have to create a specific DAX expression for each combination of Period calculations and measures that you want to support. This is not flexible and scalable as a more generic solution could be.

You have to evaluate case by case whether or not these drawbacks make the implementation of a Period table a good option.

Calculation Parameters Using an Unrelated Table

In the last section, you saw us apply a technique that depends on a table in the PowerPivot model that does not have any relationships with other tables in the same model. In general, this technique might be useful as a way to pass information to a measure as if it were a parameter. For example, imagine that you define a table in Excel that contains all the integers from 1 to 10, and then you import this table into PowerPivot, calling it SimulationParameter. At this point, you might use the value selected in this table in the DAX expressions of your measures, using that number as if it were a parameter passed to your formula. Moving that table into a slicer would be a convenient way for an end user to look at results by changing the selected value.

Save to your account