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],

0, 1)