Once you have a master calendar you’ll often find yourself creating complex set analysis to calculate date ranges or comparative calculations like Month to Date or Previous Month. You can find some good examples here:
Firstly a standard Calendar is created using AutoGenerate. I also take the opportunity to incorporate Business Day flags (set by loading regional public holidays).
A Cartesian Product is then produced by left Joining Distinct Dates with itself without the key. This gives us all the possible date combinations.
I then create the flags; Firstly Point In Time Flags such as Today, This Week, This Month, etc. Then period flags such as Previous Day, Previous Month, Previous Month to Date, etc.
Update - Following the comments I've added more code to the CLEAN UP tab to remove any lines where no flags have been set and any lines where the Possible Date is higher than the selected date which reduces the tables size by over 50%
Update - Fixed a bug resulting from the previous update
Update 22-Aug-2014 - Fixed a bug which reduces the number of rows in the final calendar
I hope this of some use to people, I would love to hear your comments and further suggestions.