Microsoft Office Tutorials and References
In Depth Information
The FILTER call filters only the ShipDate row in the OrderDate table. Using this filter, the
CALCULATE function returns the value of WorkingDayNumber for that row. The use of
VALUES grants that an error message is raised if the FILTER returns more than one row
(in which case, the filter condition contains an error).
So using this DAX expression for both ShipDate and DueDate, we can define a DueDeltaDays
calculated column in SalesOrderHeader by using the following formula:
DueDeltaDays = CALCULATE( VALUES( OrderDate[WorkingDayNumber] ),
OrderDate[Date] = SalesOrderHeader[ShipDate])) + 4
- CALCULATE( VALUES( OrderDate[WorkingDayNumber] ),
OrderDate[Date] = SalesOrderHeader[DueDate]))
The DueDeltaDays column shows a positive number in the case of a delay, representing the
number of delay days. Negative numbers indicate an early delivery (measured always in days).
In Figure 7-34, you can see values for this column and for another calculated column named
DeliveryDelayDays, which displays a value only for delayed orders.
FIguRE 7-34 The DueDeltaDays and DeliveryDelayDays calculated columns in the SalesOrderHeader.
With this information, you can calculate some measures in the PivotTable, such as the ratio of
DeliveryDelayRation = COUNT( SalesOrderHeader[DeliveryDelayDays] )
/ COUNTROWS( SalesOrderHeader )
You can also calculate the average delay (in days) for delayed orders, by simply selecting the
Summarize By Average item on the DeliveryDelayDays column. In Figure 7-35, you can see a
PivotTable displaying both these measures.