I have a cube that shows sales opportunities. Every month, it gets updated
with the latest status of each opportunity.

Example data:
April: Opp 1, $3,100
May: Opp 1, $3,500
Oct: Opp 2, $8,500
Nov: Opp 2, $8,500
Nov: Opp 3, $7,500

As you can see, once a sale is completed, it is not added to the cube for
the following months. This way I can determine the duration of the sales
process, etc.

What I can't get is the amount to sum the way I want. I tried using the
Measures.[Last Non Empty Value] example from the semi-additive measures
example. In November, I want the Amount to roll-up to $19,500: the last
amount of each opportunity. However, it only rolls-up to $16,000, presumably
because the Opp 1 value is 0 during the single last non-empty month that is
found (which would be Nov at the opporunity level). The drill-down looks
fine, each opportunity has an amount.

How should I change the MDX so it rolls up to $19,500?

Measure: Last Amount
CoalesceEmpty((Measures.[Raw Amount], [CRM Period].CurrentMember ) ,
(Measures.[Last Amount],[CRM Period].CurrentMember.PrevMember))

(Raw Amount is a SUM measure of all the amounts. CRM Period is my time
dimension).


I tried this with the Closing Value semi-additive measure with the same
results.

You can sum the leaf [Last Amount] over opportunities:


Sum(Descendants([Opportunity].CurrentMember,,LEAVES),
CoalesceEmpty(
(Measures.[Raw Amount], [CRMPeriod].CurrentMember),
(Measures.[Last Amount], [CRM Period].PrevMember)))


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Oops - omittted the recursion termination condition:


iif(IsLeaf([Opportunity].CurrentMember),
CoalesceEmpty(
(Measures.[Raw Amount], [CRMPeriod].CurrentMember),
(Measures.[Last Amount], [CRM Period].PrevMember)),
Sum(Descendants([Opportunity].CurrentMember,,LEAVES),
Measures.[Last Amount])



- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ok, I am it is real close. Thanks for your help. This is what I am
currently working with:

Example data:
April: Opp 1, $3,100
May: Opp 1, $3,500
Oct: Opp 2, $8,500
Nov: Opp 2, $8,500
Nov: Opp 3, $7,500

iif(IsLeaf([CRM Client Opportunity].CurrentMember),
CoalesceEmpty( (Measures.[Raw Amount], ClosingPeriod([Month])),
(Measures.[Last Amount], [CRM Period].PrevMember)),
Sum(Descendants([CRM Client Opportunity].CurrentMember,,LEAVES),
Measures.[Last Amount])
)

I changed the [CRM Period].CurrentMember to ClosingPeriod([Month]) because
when I rolled up to the Quarter or Year level, it took the sum of every
month.

In this example, my fiscal year starts in August. So in Q2 (which just has
one month - November), it correctly rolls up to $19,500. Q1 Rolls up
correctly to $12,000. The problem is the year level only rolls up to
$16,000. I would expect to $19,500.

I think the problem is the [CRM Period].PrevMember) clause. Because at the
year level, the previous member goes from fiscal 2005 to 2004 and there is
nothing before that. So the $3,500 from Opp 1 never gets rolled up to the
year level with this query.

Assuming I am diagnosing this correctly, I think the answer would be to
change [CRM Period].PrevMember to essentially be the previous month. If
that's the case, how do I do that?

Thanks for your help.

Your diagnosis appears to be correct - the PrevMember may not be
operating at the lowest level. One option is:

CoalesceEmpty(Measures.[Raw Amount],
(Measures.[Last Amount], [CRM Period].PrevMember)),
Sum(Descendants(
[CRM Client Opportunity].CurrentMember,,LEAVES),
(Measures.[Last Amount], ClosingPeriod([Month]))

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!