Microsoft Office Tutorials and References
In Depth Information
This formula uses the CALCULATE function by applying a filter context to SalesOrderHeader
based on OrderDate and ShipDate so that the rows that are counted by ROWCOUNT are only
those that match both filters.
Explaining the OrdersInPlace Measure
We suggest that you use the CALCULATE function in the OrdersInPlace measure for
performance reasons. It would be certainly possible to directly use the COUNTROWS
function passing a FILTER as a parameter, but this would result in a slower execution.
Using the CALCULATE function, you can modify the filter context on SalesOrderHeader, which
is then counted by the COUNTROWS function. Because there are no relationships between
the Calendar and SalesOrderHeader tables, without any other filter in the CALCULATE call, the
returned value would always be the same, regardless of any Calendar date selected in the
PivotTable. The first FILTER returns a set of OrderDate values and the second FILTER returns
a set of ShipDate values, using a very similar logic. For each of the OrderDate dates that are
available in SalesOrderHeader, which can be filtered by other active filters in the PivotTable,
only those that are less than or equal to the selected date are returned. In case of a selection of
a single date in Calendar, the MAX( Calendar[Date] ) expression returns just that date. However,
in case of a period such as a year or a month, the requirement is to return the same value that
would be visible on the last date of that period, which is exactly the date obtained by using the
MAX function. The filter for ShipDate works in a similar way, except that only ShipDate dates
that are greater than or equal to the selected date are returned.
In Figure 9-23, you can see the resulting PivotTable with a corresponding chart of the
OrdersInPlace measure calculated at a day level.
FIguRE 9-23 The OrdersInPlace measure calculated at a day level.
The same calculation can be made at a different granularity, such as the OrdersInPlace
measure calculated at a month level in Figure 9-24. Remember that, in this case, you are
considering just the last day of each month in this visualization.