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] ),
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,
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,
/ 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.
Search JabSto ::

Custom Search