Hello.

My apologies for this post as I've searched through previous posts and
have found posts related to my problem but have been unable to get
exactly what I want.

I'm looking at personnel headcounts over over time dimension.

ie. Dimension Time with 3 levels. Year, Term, Month

I have a calculated member called Headcount, which is as follows:-

Iif([Time].Currentmember.Level.Name = "Year",
([Time].Currentmember.LastChild),
Iif([Time].Currentmember.Level.Name = "Term",
([Time].Currentmember.LastChild),[Measures].[Headcount]))

This displays the Headcount number on a monthly level and when
aggregating to a Term level, takes the figure from the last month.
Similarly, when aggregating to a Year level, the last Term figure is
displayed.

This is all fine and dandy, except when there are non values at a
monthly level i.e. we have not yet come to the end of the Term.

Can anyone help me modify the above MDX for my calculated member, to
display (when at a Term level) the last non-empty month figure ?

By way of an example:

Year Term Month Headcount
2004 11
2 11
Oct 10
Nov 11
Dec 12
Jan 13
Feb 12
Mar 11

2005 11
1 11
Apr 10
May 11
Jun
Jul


In the above example, the 2005 Term 1 total is 11 (i.e may's figure) as
there are not yet figures for June or July.

Any help much appreciated.
Regards,
J.