Often times, you want to analyze sales using a year to date view as shown below:
While you can do this in an MDX formula in SSRS, you might find this calculation valuable enough to store a server-side calculation. To do this, you'll need to pick what date dimension or role playing dimension you want to slice this by. After you know that, you can use an MDX calculation as shown below for this:
Sum(YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])
This will show a rolling YTD for any given date level in my Delivery Date role playing dimension. In other words, If I'm at the year level, I'll see entire year's sales. As I drill in to March, I'll see only the sales up to March. In my example, [Delivery Date].[Calendar] represents a user hierarchy so I can then point to any level of the hierarchy.
I'm using this calculation (well, PeriodtoDate, to be exact as I'm dealing with Fiscal years) and have an issue when months are missing.
Say in your example there was no fact value in the cube against Feb 2002, this would return no row against this missing month. In Reporting Services, a report which filters on Feb 2002 would then return nothing for the YTD to Feb 2002 value, which is wrong as it should be $648k (January's value).
I'm really struggling with this so any help would be much appreciated.