Hello All,

I am desperately trying to get a sum of certain transactions since the
effective date of a prior market value.

I have an accounts dimension with capital calls and market value
I have a time dimension

I have a fact dimension with USDamount and effectivedate


The following works when I hard code it

sum(
filter(
crossjoin(
{[time].[day].members},
{[account].[capital call]}
),
[measures].effectivedate > cdate("3/31/2003")), USDAmount)



HOWEVER when I want to the effective date of the last Market Value it shows
no error in the calculated member builder by it returns ERR

sum(
filter(
crossjoin(
{[time].[day].members},
{[account].[capital call]}
),
[measures].effectivedate > cdate(([Account].[Last Market Value],
[Measures].[Effectivedate]))), USDAmount)



If I leave the "cdate" out, I get an answer back, but its not at all what I
would expect.

Any suggestions?

Thanks

Eric

"Eric Bowden" <ehbowden (AT) nospamyahoo (DOT) com> wrote

1. What is the data type of EffectiveDate measure ?
2. Could you define EffectiveDate as another dimension, not as measure ?
3. CDate translates strings to dates, since ([Account].[Last Market Value],
[Measures].[Effectivedate]) is not a string, the conversion fails.

HTH,
Mosha.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================