Hi,
I have posted this question serveral days ago and have not received a
respond; therefore, I am posting it again. Any assistant and/or help is
greatly appreciated.


I am having some difficulty creating a calculated measure in MS SQL 2000
Analysis Services that will produce "Year End Forcast". Basically I need to
annualize the current YTD Charge Amt (comission) by dividing by the number of
months that have past already and then multiplying it by 12 months.

YTD Charge Amt =
SUM(PeriodsToDate([PERIOD].[Year],[PERIOD].CurrentMember),[Measures].[Charge
Amt])

Any help or assistant is greatly appreciated...

Regards
Fernando Sanchez

Hi Fernando,

You can try:

[Measures].[Year End Forcast] =
* Count(Descendants(Ancestor(
[PERIOD].CurrentMember,
[PERIOD].[Year]),
[PERIOD].CurrentMember.Level)))
/ Count(PeriodsToDate(
[PERIOD].[Year],
[PERIOD].CurrentMember))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,
Thanks for responding...You have help a great deal. I have created the
calculated measure as you suggested. It work fine if I drill into the last
month that I have data for; any other month, the "Year End Forecast" is
different. Currently, I only have two month of data for the current year.
Therefore, my "Year End Forecast" should as follows:

JAN $4,586,976.00
FEB $5,106,216.00
Ytd $9,693,192.00
YEF = (($4,586,976.00 + $5,106,216.00)/2)*12
= ( $9,693,192.00/2)*12
= $4,846,596.00 * 12
= $58,159,152.00

If my period is Mar and I do not have any data, the YEF should still be
$58,159,152.00. Also, If my period is 2006, the YEF should still be
$58,159,152.00



"Deepak Puri" wrote:

Fernando,

The behavior you're describing is different than my original
interpretation. Here's an expression to extrapolate Charges for the
current year, regardless of which member of the year is selected. You
can further refine it to meet your needs:

[Measures].[Year End Forcast] =
([Measures].[Charge Amt] *
Count(Descendants(PERIOD].CurrentMember,,LEAVES)))
/ Count(NonEmptyCrossJoin(Descendants(
PERIOD].CurrentMember,,LEAVES))))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,
I am trying to create the calculated measure recommend below and I am
getting an error.

"Deepak Puri" wrote:

Fernando,

Here's a sample Foodmart query which may help:

'Sum({Ancestor([Time].CurrentMember,Time.[Year])},
([Measures].[Unit Sales] *
Count(Descendants([Time].CurrentMember,,LEAVES)))
/ Count(NonEmptyCrossJoin(Descendants(
[Time].CurrentMember,,LEAVES))))'
select {[Measures].[Unit Sales],
[Measures].[Year End Forecast]} on 0,
Descendants([Time].[1997]) on 1
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,
I already have the YTD CHARGE AMT...All I need to know is the count for the
months that have data or the cell that are not empty within calendar year at
the lowest level. Since I only have two month loaded for the current
calendar year, I then take the YTD CHARGE AMT/2. Afterward then I multiply
it by the number of leave level member in the calendar year. In this case it
is 12 months. My period is broken down by YEAR, QTR, MONTH. Hope this help.

"Deepak Puri" wrote:

Fernando,

I'd suggest trying the query I posted - [YTD CHARGE AMT] may not be that
relevant, based on your description. For example, if data is loaded for
Jan and Feb, but the current time member selected is Jan, I assume that
you still want to extrapolate the forecast charge based on both Jan and
Feb data. But [YTD CHARGE AMT] will only reflect Jan data.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

You are right...I will only give me the YTD Charge Amt for the period
selected...Which query are you referring to? The one prior to the FoodMart
is giving me errors.

"Deepak Puri" wrote:

I was referring to the Foodmart query - it's based on the prior
expression (which isn't itself a query); but the syntax is likely to
work, since the query executes.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***