Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SSAS MDX Year to Date (YTD) Calculation Example

  • 18 December 2009
  • Author: Brian Knight
  • Number of views: 75980
  • 0 Comments

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.

 

 

Print
Tags:
Rate this article:
3.3
Brian Knight

Brian KnightBrian Knight

Other posts by Brian Knight

Please login or register to post comments.