PowerPivot Alchemy: Patterns and Techniques for Excel by Collie Rob & Jelen Bill

Author:Collie, Rob & Jelen, Bill [Collie, Rob]
Language: eng
Format: epub, pdf
Publisher: Holy Macro! Books
Published: 2021-02-07T16:00:00+00:00

Common Calendar Conundrums

A tour of common calc column scenarios would not be complete without some time in calendar land, so that’s our last stop in this chapter.

Using an IsFutureDate Column

Here you see that the most recent date in the ServiceCalls table is 6/1/2004:

Figure 3.35

But the Calendar table extends beyond that, into August 2004:

Figure 3.36

It is often very helpful to have a calculated column in a Calendar table that flags dates as being in the future, from the perspective of your data tables. Here is one such formula:

=IF(Calendar[Date]>MAX(ServiceCalls[CallDate]),"Yes","No")

When you add this formula to the Calendar table, it starts returning "Yes" on 6/2/2004:

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.