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

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