Microsoft Office Tutorials and References
In Depth Information
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.
Tip Remember that you cannot hide a measure from a PivotTable (you can hide only calculated
columns). So if you do not want to expose internal calculations, you should expand all the
measures included in the preceding CalcLineTotal expression.
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.
Search JabSto ::




Custom Search