Microsoft Office Tutorials and References
In Depth Information
Working Days in Different Countries
In the examples included in this section, we are making some wrong assumptions for
a database like AdventureWorks, but that might be good for your own data. In fact,
because we are accounting for sales in stores located in different countries, we should
consider a different number of working days for each of these countries. This would make
the DailySales measure harder to calculate. In fact, we should accomplish all this as well:
■ Define a separate table to calculate working days, based on country and date.
■ Make a calculation of the required DailySales average by country.
■ Aggregate that number for all countries by using a weighted average based
on the sales amount for that country in a given period.
Although this calculation is still possible, it is very complex and it is seldom used because
this measure is probably not the same for different countries in the same report.
Our technique up to now is really error prone because we write directly into a cell a value of 0
to indicate a holiday, without any further explanation. If we make an error, it is really hard to
identify; furthermore, we make no distinction between weekend days (which are automatically
calculated) and holidays. A better solution is to define a separate Holidays table, which is easier
to check and to maintain because it moves into a single calculated column the logic to merge
weekend evaluation and holiday definition using a single formula. In Figure 7-28, you can see
such a Holidays table, defined in Excel.
FIguRE 7-28 Holidays table in Excel.
You can import this Holidays table as a linked table in PowerPivot and define a relationship
between the OrderDate and Holidays tables, as shown in Figure 7-29.