Hi,

Every month i am getting YTD values. I would than like to calculate the
actual month values in AS. For example:

Month YTDValue ActualMonthValue
------------------------------------------------------------
January 5 5
February 10 5 (February -/- January)
March 13 3 (March -/-February)

and so on....

What's the mdx for this? Also is it maybe easier to calculate it in the view
in SQL Server instead in AS?


Thnx,

Stanley

Hi Stanley

You can make a calculated member ActualMonthValue with the formula:
[Measures].[YTDValue] - ([Measures].[YTDValue], [Time].PrevMember)

(Obviously using the actual name of your time-dimension)

The "([Measures].[YTDValue], Time.PrevMember)" gives you the YTDvalue
from the previous member in the time-dimension.

If you prefer, you can, using the same basic principle, alternatively
make the calculation dynamically in your MDX-query, e.g.:

with member [Measures].[ActualMonthValue] as '[Measures].[YTDValue] -
([Measures].[YTDValue], Time.PrevMember)'
select
{[Measures].[ActualMonthValue]} on columns,
{[Time].members} on rows
from [Your Cube]

I can't imagine, that it is easier to make the calculation in a SQL view
(these kind of calculations being one the major strongpoints i MSAS and
other OLAP-engines), but it may be faster. This because the result a
calculated member isn't stored in the cube - only the formula. The
result will be calculated each time, it is accessed.

If, on the other hand, you've made the calculation in advance (from MSAS
point of view) in a SQL view, the result will be stored in the cube,
thus making it faster to access - theoretically anyway.

But still, I have made some very BIG cubes with a LOT of calculated
members and I haven't had any problems worth mentioning with them.

And then again, the fact that only the formula is stored in the cube, if
you make a calculated member, means that it consumes less disk space.

These considerations are only relevant, if you have a "large" cube and /
or a "lot" of complex calculated members, otherwise it doesn't make any
difference.

Best regards
Dan Reving

Stanley wrote: