MDX-Why YDT function sometimes return incorrect results for measure with NON_EMPTY_BEHAVIOR?
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.