Microsoft Office Tutorials and References
In Depth Information
FIguRE 7-35 The DeliveryDelayRatio and average of DeliveryDelayDays columns in PivotTable.
Calculating WorkingDays by using Table Relationships
You might wonder why we have not used table relationships to relate SalesDate and
DueDate tables with corresponding SalesDates and DueDates tables; why have we
used a more complicated DAX expression instead? We have two reasons for that. First,
we would have needed to duplicate these two tables both in Excel and in PowerPivot.
Second, the need to duplicate would have been propagated to the Holidays table too,
requiring three Holidays tables in Excel that would have been imported in three
different linked tables in PowerPivot. You can look at an example of such a model in the
CH07-07-DeliveryDays-UsingRelationships.xlsx workbook included on the companion DVD.
Because PowerPivot does not support more than one relationship between two tables,
it follows in an indirect way that you cannot relate the same Holiday table from two or
more different tables (such as SalesDates and DueDates) if these tables are both related
to the same table (such as SalesOrderHeader), even if the relationship is through different
Aggregating and Comparing over Time
Working days calculation is only the first step in the benefits that you can obtain by using a
calendar table. In the next sections, we introduce other useful techniques. It is often required
that you analyze particular aggregations of values over time. For example, you might want to
calculate the aggregated value of a measure from the beginning of the year up to the period
you are selecting. (This is commonly called year-to-date aggregation .) You might want to look
at the Sales Amount for the month of March but also want to look at the total Sales Amount
from January to March. Having a Dates table is an important prerequisite for making this
calculation in PivotTable.