I'm still new to MDX, so hopefully this isn't such an obvious thing.

Let's say in my 2005 cube, I have a Sales Fact table with an Invoice Date dimension, where they are linked via [invoice date_key].

I then have a Calculated Member in my cube for "Rolling 12 Month Margin $" defined as:

SUM( LASTPERIODS(12, [Invoice Date].[Invoice Month]) , [Measures].[Margin $] )

Essentially, this sums the margin $ for each invoice for the last 12 months, with the last month of the 12 month period being the previous month. For example, for December 2005, the 12-month period of the "Rolling 12" period is December 2004 through November 2005.

With a BIG assumption that the data in the Sales Fact table is good, there are a lot of records that have a value for [Margin $], yet have NULL for [inovice date_key] and this is giving me confusing results from my calculated member mentioned above.

The following SQL query yields the correct result in my mind:

SELECT SUM([Margin $]
FROM [Sales Fact] SF
INNER JOIN [Invoice Date] IDT
ON SF.[Invoice Date_key] = IDT.[Invoice Date_key]
WHERE
IDT.[Invoice Date] >= '2004-12-01' AND IDT.[Invoice Date] <- '2005-11-30'

However, it appears in my cube that the calculated definition is summing up those records that have NULL for [invoice date_key] but have a value for [Margin $]. I guess I assumed or expected that those would be filtered out and that the MDX would ultimately mimic the SQL above.

Can someone explain how and why the MDX/my calculated member doesn't filter out those records where the [Invoice date_key] is null yet has a value for [Margin $]?

I apologize if I haven't provided enough information, so please let me know what other information is needed.