SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: Why YDT() function sometimes return incorrect results for measure with NON_EMPTY_BEHAVIOR defined property?

A: This question was answered by Mosha Pasumansky in MSDN forum thread:

NEB almost always cannot be used with YTD, because it is practically guaranteed to produce wrong results. Let's consider the following example:

YTDSales = SUM(YTD(), Sales)

Now, why is it wrong to define NON_EMPTY_BEHAVIOR=Sales on YTDSales measure. Let's suppose that we have following data:

Sales YTDSales
January 10 10
February 5 15
March NULL 15
April NULL 15

What would be the result of Query

SELECT Sales ON 0, NON EMPTY Month.MEMBERS ON 1 FROM cube

Obviously we want to have just January and February. But in the following query

SELECT YTDSales ON 0, NON EMPTY Month.MEMBERS ON 1 FROM cube

We want to get all of the months. Now, defining NEB of YTDSales to be that of Sales will result in the second query returning only January and February. This is obviously wrong.

Feb 27, 2008: Read here workaround example posted by Chris Webb.