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.
Search JabSto ::

Custom Search