Please help!

I built two dimensions on a "Time" table, [Begin] - a regular
dimension and [End] - a time dimension. Levels and members are
identical. "Time" table is linked to "TradeDate" column in
"Transaction" fact table.

One calculated member [Measures].[DayCount]:

count(LinkMember(OpeningPeriod([Begin].[Day],
[Begin].currentmember),[End])
:ClosingPeriod([End].[Day], [End].currentmember))

Another calculated member [Measures].[Cumulative]:

sum(LinkMember(OpeningPeriod([Begin].[Day],
[Begin].currentmember),[End]):
ClosingPeriod([End].[Day], [End].currentmember),
[Measures].[Quantity])

In Analysis Manager Cube Browser, [Begin] and [End] are at top (page
filter fields).

Here is the problem, [DayCount] is also correct no matter what I
select in [Begin] and [End]. But [Cumulative] is only correct if
[Begin] is left at default [All Time]. If I select [Begin] to any
level or member, [End] is ignored.

What's going on? How does MDX handle two arbitrary days, a very common
financial calculation?

Thanks,

Bo

Based on the description of how you set up [Begin] and [End] dimensions,
the only leaf cube cells (at the day level) that hold data have linked
[Begin] and [End]. This doesn't affect [Measures].[DayCount], since it
is only based on the 2 dimensions, not on cell data.

But for [Measures].[Cumulative], a given [End] day only holds data for
the linked [Begin] day and its ancestors (incl. [All Time]), so override
the [Begin] member:

[Begin].CurrentMember),[End]):
ClosingPeriod([End].[Day],[End].CurrentMember),
([Measures].[Quantity],[Begin].[All Time]))

- Deepak

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

Thank you so much. That's exactly what I needed.

Bo

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