Microsoft Office Tutorials and References
In Depth Information
After you rename your column, you can create a report similar to the one you saw in Figure
7-19, but this time, use more meaningful names for columns that were ambiguous before.
You can see the result of such a process in Figure 7-23.
FIguRE 7-23 Column prefixes are more recognizable both in the slicers and in the PowerPivot Field List.
We suggest that you use column prefixes every time you have the same column name in
different tables—not just for Dates tables.
Calculating Working Days
Now that you have learned how to create a calendar table, it is worth pointing out some
columns that can be very useful in data analysis and that can be conveniently stored in
the calendar table. For example, you might be interested in defining a measure that
calculates the average of sales per working days in a given period. (You can find the complete
example in the CH07-04-WorkingDays.xlsx workbook included on the companion DVD.) To
do that, you have to calculate the number of working days, which in turn requires knowing
whether a day is a working day. The simpler way to do this is to add a WorkingDays column
to the Excel OrderDate table. That column should have the value 1 for working days, and
0 for holidays, weekends, and other nonworking days. Instead of compiling this column by
hand, you might define it by using the following Excel formula that assigns 1 to all week
days between Monday and Friday, leaving 0 to Saturday and Sunday:
= IF( WEEKDAY([@Date],2) > 5, 0, 1 )