Microsoft Office Tutorials and References
In Depth Information
Tip You might want to use a separate NonWorkingDays table to configure the working days in
the week. In that case, you use the VLOOKUP Excel function in the preceding expression. You see
a similar example later in this section, when we discuss how to create a table that defines public
holidays that must be differentiated from working days.
This formula is automatically copied into all the rows of the OrderDate table, as you can see
in Figure 7-24.
FIguRE 7-24 The WorkingDays column added to the OrderDate table in Excel.
You can modify single values for other nonworking days, such as public holidays, overriding the
formula with a forced fixed value (usually 0) just for these days. For example, in Figure 7-25, you
can see the value for January 1, 2001 overridden by a 0 value, whereas the following dates are
still evaluated by the formula we defined before.
FIguRE 7-25 The value 0 overriding the formula for January 1, 2001.
Note The warning shown in Figure 7-25 to the left of cell J2 indicates a possible inconsistency in
a column that contains a formula. You can click the Ignore Error item in the context menu to turn
off the warning.
You can update the OrderDate linked table in PowerPivot, and the WorkingDays column
shows up in the PivotTable too. At this point, you can define a measure belonging to the
SalesOrderDetail table named DailySales, which divides the sum of LineTotal by the sum
of working days, as you can see in Figure 7-26.