Recently, I was working with a client we were working on a calculated measure using the aggregate function to rollup a periodstodate calculation using a calculated measure. The value repeatedly returned null, but when I used SUM it worked fine. This appears to be directly related to an issue Microsoft identified in KB970461 – http://support.microsoft.com/kb/970461
This does not affect SQL 2005 and only certain service pack levels of SQL 2008 SSAS. The behavior is described in the KB as
In Microsoft SQL Server 2008 Analysis Services, you run a Multidimensional Expressions (MDX) query that contains the Aggregate function to query a calculated measure. However, this query incorrectly returns a NULL value for the calculated measure.
The issue is resolve in Cumulative Update Package for SQL Server 2008 SP1. http://support.microsoft.com/kb/970315/LN/
