Hi,

I have current balance that get updated once every month and we show the
latest balance as of a given month to the users. I have a simple formula:

If I am at all time level, I need to use the time default member, which is
set to be the latest month in the cube dynamically.

iif(([Time].CurrentMember.Level is [Time].[All Time].Level),
([Measures].[Balance Am], [Time].DefaultMember),
([Measures].[Balance Am],ClosingPeriod([Time].[Month],[Time].CurrentMember)))

This calculated measure works fine unless I call it in the context of
aggregation of time periods. Some of the users have access through Office Web
Components Pivot tables.

The problem is that Users select other regular additive measures such as
revenue and this semi-additive Balance and when they select multiple time
months, the balance comes out wrong. I think this is because the OWC seem to
perform an MDX 'Aggregate' of selected time periods and the formula I have is
returning the balance for the DefaultMember of the time. This is wrong when
the users would have chosen some earlier time periods not including the
current one.

Hope I was clear.
Thanks
Murthy.

Excel is adding a calculated member for multiple time selections at the
"All" level, so you can change the iif() to test for the specific [All
Time] member. But since the calculated member has no natural
ClosingPeriod(), some other balance, such as average, would have to be
used:

iif([Time].CurrentMember is [Time].[All Time],
([Measures].[Balance Am], [Time].DefaultMember),
[Measures].[Balance Am]/[Measures].[BalanceCount]),
([Measures].[Balance Am],
ClosingPeriod([Time].[Month],[Time].CurrentMember)))

- Deepak

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