# How Is the Following Calculated Member Interpreted? Getting Unexpected Results...

• ### General discussion

• 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.

Wednesday, December 7, 2005 9:38 PM