Microsoft Office Tutorials and References

In Depth Information

FIguRE 7-32
The Dates column in the SalesOrderHeader table.

Calculating whether an order has been shipped on time seems pretty easy: you should just

compare the DueDate and ShipDate columns. However, if you consider a standard delivery

time of four working days, you should calculate how many orders have been shipped after

DueDate minus four working days. This calculation requires the support of the Dates table.

The complete model of the following example is available in the CH07-06-DeliveryDays.xlsx

workbook included on the companion DVD.

To make the calculation, we need to add a calculated column in the SalesOrderHeader table

that calculates for each order the difference (in working dates) between the two dates. You

can create a WorkingDayNumber calculated column in the Dates table that has the following

formula:

WorkingDayNumber =SUMX( FILTER( OrderDate,

OrderDate[Date] <= EARLIER(OrderDate[Date]) ),

OrderDate[WorkingDays] )

This number calculates for each day the number of working days elapsed since the first date

in the Dates table. In Figure 7-33, you can see how this number is calculated for a few rows.

FIguRE 7-33
The WorkingDayNumber calculation.

At this point, you can define the number of working days between two dates using the

difference of WorkingDayNumber for the correspondent dates. Because you might not want

to add too many tables to the PowerPivot model, you may reuse the same OrderDate table

already imported into the model to get the WorkingDayNumber for both DueDate and

ShipDate dates of an order. For example, this number for ShipDate can be obtained by

using the following DAX expression:

CALCULATE( VALUES( OrderDate[WorkingDayNumber] ),

FILTER( OrderDate,

OrderDate[Date] = SalesOrderHeader[ShipDate] ) )