Microsoft Office Tutorials and References

In Depth Information

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.