Here is the problem I am having. I have a measure the measures the $ amount
growth from this year to last year for same time period (week, month,
quarter, year).

The problem arries when for example the user has quarter 2 selected of 2004
and this quarter is not yet done and then the subtration to quarter 2 of
last year occurrs. it shows negative growth ofcourse becuase this years
quarter 2 is not over yet. Is there anyway that I can check to see if the
time period is complete and if it is not only allow the same partial time
period of last year to be compared to this years.

-mike

Here's an approach for just a single [MyDate] member selected:

'Sum(NonEmptyCrossJoin(Descendants([MyDate].CurrentMember,,LEAVES)),
(Measures.Amount,ParallelPeriod([MyDate].[Year])))'
Member Measures.Growth as
'(Measures.Amount-Measures.PrevAmount)/Measures.PrevAmount',
FORMAT_STRING = 'Percent'

Select {Measures.Amount, Measures.Growth} on columns,
[2004].Children on rows

From MySales
- Deepak



"Mike Morse" <mike.morse (AT) micromo (DOT) com> wrote

Hi Mike,

How is the issue going on your side? Does Deepak's suggestion meet your
requirements? Let us know if you need further assistance on this issue.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

I'm sure it's doable, but how is it known that a quarter is complete?
Existence of data in the 1st month of the folowing quarter?

public @ the domain below
www.tomchester.net

"Mike Morse" <mike.morse (AT) micromo (DOT) com> wrote

Hi Mike,

This is one approach that you can take using a calculated member:

IIF(IsGeneration(Time.CurrentMember,0)
or IsGeneration(Time.CurrentMember,1) and
Count(Time.CurrentMember.Children)=3
or IsGeneration(Time.currentMember,2) and
count(Time.CurrentMember.Children)=12,[Measures].[Count of Cases]-
IIF(IsGeneration([Time].Currentmember,0),
ParallelPeriod([Time].[Month]),
IIF(IsGeneration([Time].Currentmember,1),
ParallelPeriod([Time].[Quarter]),
IIF(IsGeneration([Time].CurrentMember,3),

ParallelPeriod([Time].[Year]),0))),RollupChildren(Time.Currentmember,"+"))

By way of explanation what we're doing is checking the generation of the
time member, and if it happens to be the month member or quarter member
with three children or the year member with 12 children, then we can use a
ParallelPeriod function call. If the time.currentmember doesn't meet those
criteria, we'll want to get the value by rolling up the values associated
with the children of either the quarter or the year.

The reason for checking the number of children associated with the year is
that if the current year has fewer than 12 children, we're going to be
comparing the current partial year with the entire previous year.

Regards,


John Desch, MCDBA, MCSD
Microsoft SQL Analysis Services Support

This posting is provided “AS IS”, with no warranties, and confers no rights.