Microsoft Office Tutorials and References
In Depth Information
FIguRE 7-29 The relationship between the OrderDate and Holidays tables.
You can remove the WorkingDays column because you are moving the whole logic
into PowerPivot. Because you need to use the RELATE function to get holiday
information in PowerPivot, you should move all the business logic into one simple place:
avoid splitting it half and half between Excel and and PowerPivot. After you update
the OrderDate table in PowerPivot by removing the WorkingDays data column, you
can define a new WorkingDays calculated column by using the DAX formula that
you can see in Figure 7-30.
FIguRE 7-30 The WorkingDays calculated column in the OrderDate table.
Let us examine the DAX formula in Figure 7-30. First of all, you can see a new Holiday
calculated column defined by the following formula:
Holiday = IF( ISBLANK( RELATED(Holidays[Date]) ), FALSE, TRUE )
The Holiday column has a TRUE value for every day that corresponds to a holiday in the
Holidays table. Using this information, we extend the previously defined Excel formula
that considers whether a nonworking day is a Saturday, a Sunday, or a holiday by using
the following DAX formula:
WorkingDays = IF( WEEKDAY( OrderDate[Date], 2 ) > 5 || OrderDate[Holiday],