Month's end

Hi,

I am working on a cube that contains month standings and month totals. This works fine if I select one month.

It goes wrong when I do a year to date selection because it sums up the month standings.

How can I make a cube that does sum up the month total but only returns the last month standings.

With regards,

Constantijn Enders

Hi Constantijn,

If you configure the "month standings" measure (I don't know its details) with the aggregate function: LastNonEmpty, and you use Aggregate() rather than Sum() in your "year to date" calculation, does that work?

http://msdn2.microsoft.com/en-us/library/ms175623.aspx

>>

SQL Server 2005 Books Online

Configuring Measure Properties

Measures have properties that enable you to define how the measures function and to control how the measures appear to users.

...

Property

Definition

AggregateFunction

Determines how measures are aggregated. For more information, see Aggregation Functions.

LastNonEmpty

Semiadditive

Retrieves the value of the last non-empty child member.

>>

|||Or, depending on your data, you may want to use more optimal LastChild aggregation function.sql