Im trying to create a cube in the cube editor but i cant
work out how to get the value in the last valid cell of a
month.

Say I have the following

1st Jan 2nd jan 3rd Jan 4th jan

TeamA 10.00 20.00 20.00 5.00
TeamB 50.00
TeamC 30.00 40.00

1st Feb 2nd Feb 3rd Feb 4th Feb

TeamA 110.00 120.00 120.00 15.00
TeamB 150.00
TeamC 130.00 140.00


What Im trying to do is create a calculated member that
returns the last value for the Team.

Ive been using :-
([Measures].[Day General Balance],closingperiod([Date].
[Day],
[Date].currentmember))

However this returns the value for the last day ie:
TeamA = 5.00, TeamB =<empty>, TeamC =<empty>

What I want is for the calculated member to return the
following. teamA = 5, TeamB = 50, TeamC = 40 for Jan
totals

Ive also tried using

tail(filter(date.day.members, not isempty([Day General
Balance])),1).item(0)

But this gives recursion errors so i tried :-

iif([Date].Currentmember.level.name = "Day",
([Measures].[Day General Balance], Tail( nonemptycrossjoin
( {PeriodsToDate([Date].[(All)],[Date].CurrentMember)},
{([Measures].[Day General Balance], [Team
Name].CurrentMember) }, 1 ) ).Item(0) )
,
iif([Date].Currentmember.level.name = "Month",
([Measures].[Day General Balance], Tail( nonemptycrossjoin
( {PeriodsToDate(Month,[Date].currentmember)},
{([Measures].[Day General Balance], [Team
Name].CurrentMember)},1) ).Item(0) ),
iif([Date].Currentmember.level.name = "Year",
([Measures].[Day General Balance], Tail( nonemptycrossjoin
( {PeriodsToDate([Date].[(All)],
[Date].currentmember.lastchild.lastchild)}, {([Measures].
[Day General Balance],[Team Name].CurrentMember ) },
1 ) ).Item(0) ),0)

))

This almost works, however when viewing the data at the
month level it gives the sum of [Day General Balance]
for the whole month. What I need is for it to return the
value for the last non-empty cell for the current month.

Can anyone help me ?

In the month section of the iif, try replacing:

{PeriodsToDate(Month,[Date].currentmember)} with
[Date].currentmember.Children


- Deepak

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