Hi,

I'm having trouble with creating a straightforward balance calculated
measure : If there is no "YTD Amount" for the month then carry forward
the "YTD Amount" from the previous month

I'm using the recursive solution provided by Microsoft :

Measures.[Last Non Empty Value]:
CoalesceEmpty((Measures.[Value], Time.CurrentMember ) ,
(Measures.[Last Non Empty Value],Time.CurrentMember.PrevMember))

With my measures it looks like:

Measures.[YTD Amount Calc]:
CoalesceEmpty((Measures.[YTD Amount], Time.CurrentMember ) ,
(Measures.[YTD Amount Calc],Time.CurrentMember.PrevMember))


Sample Data: there is no data for months 3,5,6,8,9,10,12

Month Amount YTD Amount
--------------------------
1 2.0 2.0
2 3.0 5.0
4 1.0 6.0
7 2.0 8.0
11 2.0 1.0

The problem is for the calculated measure I'm getting "0" for the
missing months (the same as YTD Amount). For Amount and YTD Amount, 0
is the default display for empty cells but it seems like Analysis
Services is intepreting these as non-empty values so the calculation
doesn't work

Does anyone have any idea as to what is happening?

Thanks

You should define your [YTD Amount] to be empty (not 0) when the Amount
is empty, ie. something like this:

iif(IsEmpty(Measures.Amount), Null, Sum(Ytd()))

- Deepak

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

Thanks for the reply.

We solved our problem by using an "Opening Balance" calc inside our
"Plan YTD" calc. Plan YTD = YTD([Plan]) + Opening Balance.

Ryan