SSAS : Selecting a dynamic period in a MDX query

Introduction
In many customer wishes i've encountered so far, they want a dynamic report that shows the data until now. In SQL there is a GETDATE() function avaliable to build dynamic queries with dates. IN MDX there is a NOW() function available. In this small post i'll show you an example of a MDX query with a sliding window principle.

Solution
How could you solve this with MDX? For this post i've used a cube from a former post.

The most important part is the following snippet:

STRTOMEMBER('[DimDate].[Date Year].&[' + CSTR(YEAR(now())-3) + ']'):STRTOMEMBER('[DimDate].[Date Year].&[' + CSTR(YEAR(now())) + ']')

The NOW() function calculates the current date and the CSTR() funtion turns it into a string which in turn can be concatenated with the other string. The : is a range operator that returns a window from 2008 (2011 -3) until 2011 (now).

Conclusion
Very handy for building dynamic period reports with MDX.

Greetz,
Hennie