SSAS MDX Year to Date (YTD) Calculation Example

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.