Microsoft Office Tutorials and References

In Depth Information

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.