MDX expression for SSAS dynamic date dimension

Hi All,

I am hoping to create a dynamic date dimension for a SSAS cube in Visual studio. The example for them would be Current Month, Previous Month, and Current year etc. The expected result is that users don't need to manually select the from and to date for the measure and once the corresponding Dynamics date dimension is selected, the corresponding date information would apply. Any advice on how to create this kind of date dimension would be highly appreciated, thanks.


LVL 37
bbaoIT ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do you have a calendar table of any sort already?

Maybe look at this youtube video (SQL 2012 Analysis Services Calendar Dimension) which demonstrates a Visual Studio  wizard that will help you build these for you, even if you don't have 2012 it may be worth the few minutes viewing, as it touches on such topics as start of week, fiscal calendars, manufacturing calendars and so on.

For a more do-it-yourself approach jimhorn's article is relevant, and I know he also intends to expand on this topic: Date Fun, Part One:  Build your own SQL calendar table to perform complex date expressions

If you like recursive CTE's and wanted to trail a simple calendar approach you might get ideas from this tidy blog:  T-SQL: Using common table expressions (CTE) to generate sequences

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bbaoIT ConsultantAuthor Commented:
Thanks a lot for your reply. The articles are very helpful.
>>dbo.hours, dbo.minutes, etc.,
good points - see it in part2 I trust :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.