November 17, 2009 at 10:53 pm
I've created a cube in SSAS. The user enters a begin and end date range, lets say 10/7/2009 and 10/17/2009. I want to display the sum of sales for that date range, as well as the sum for the previous year during that range only (10/7/2008 thru 10/17/2008).
Or they could enter a date range of 1/1/2009 to 11/12/2009, or really anything.
1)
Getting the current date range is easy, but what MDX do I use to calculate the previous year's data for that date range only? My MDX and other MDX examples I see only get the entire previous year's data, but don't seem to know how to limit it to just the date range I show above.
2)
And as a bonus I got an even bigger challenge...instead of reporting date to date, I need to also report Day to Day...so if 10/7/2009 is the first Wednesday on October of 2009, I need to find the first Wednesday in October of 2008 and report through the end of the date range. So for example the date range of 2008 would start on 10/1/2008 because that is the first Wed, and end on 10/11/2008.
All this is easily done is T-SQL, but I don't know if MDX can do it.
November 18, 2009 at 5:59 pm
You're going to have to use ParallelPeriod(). If you have the week level in your hierarchy, that will probably work best. ParallelPeriod([week], 52, [FromDate]):ParallelPeriod([week],52,[ToDate]). Something like that should get you your range.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply