Relative Dates, Today, Yesterday, This Week, Last Week etc

A common requirement in Business Intelligence is for reports on relative dates, such as

• Today,
• Yesterday
• Same Day Last Year
• This Week
• Last Week
• This Period
• Last Period
• This Year
• Last Year

Where these are dates relative to today on the fiscal calendar. It is possible to create these members on the Time Intelligence dimension. However, since they are so widely used, and often in combination with other Time Intelligence members, there is better performance and better functionality if these members are in the time dimension. 

Putting them in the time dimension is relatively easy, all you need is an existing time dimension that has Date as a level of granularity and the ability to use a view (or the SSAS dsv) to create additional date attributes.

In your Date view, simply 

1. Add in a column for RelativeDay, another column for RelativeWeek etc. This column will have the "relative day" value. For example, 
• CASE 
•  WHEN CONVERT(DATE, GETDATE())=MyDateTable.TheDate THEN 'Today'
•  WHEN CONVERT(DATE, GETDATE()-1)=MyDateTable.TheDate THEN 'Yesterday'
• ELSE 'Other Days'
• End RelativeDay
2. Add in additional WHEN clauses for Day Before Yesterday, Same Day Last Year, Yesterday, Last Year etc if you want them. 
3. Add an attribute for each relative date into the Date dimension. One for RelativeDay, one for RelativeWeek, one for RelativePeriod etc.
4. Then, all you need to do is ensure that the Date dimension is updated early every morning. You are probably updating your dimensions many times per day. By default dimension attributes are flexible. These attributes need to be flexible as they are changing, literally every day.

From then on, cube users and reports can use these relative dates. For example, a classic use would be to have a report that filters on This Week and Last Week, and shows 8-14 columns, one for each day in the last 1-2 weeks. Whenever a user requests this report the default showing will be for the last 8-14 days, without the user having to select the actual date.

Adding these relative dates will not increase the size of your cube. The date dimension will be marginally bigger. These relative dates are easier to use than Time Intelligence and perform much better. Especially better when you want relative dates and Time Intelligence.