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] ),

FILTER( OrderDate,

OrderDate[Date] = SalesOrderHeader[ShipDate])) + 4

- CALCULATE( VALUES( OrderDate[WorkingDayNumber] ),

FILTER( OrderDate,

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

delayed deliveries:

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.