How to get the todays date in MDX
The question in the title of this post is a popular one. There are many scenarios, where it is desirable to navigate to the member of the Time hierarchy which corresponds to the current day, month or year. Sometimes, there is a desire to set the default member to be aligned with the current date. It is especially relevant, when the Time dimension contains non aggregatable attribute, such as Year (i.e. there is no member 'All Years'). By default, Analysis Services sets the default member to one of the years, but which one is undefined. So rather then override it with static member such as [Time].[Year].[2005], one may want to point to the current year. Another common scenario is around KPICurrentTime property, which is often set to today's day. Or, perhaps, calculations in the cube need to refer to today's date etc.
Tags: mdx, time dimension