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,