Last available Value from Last n Years(OLAP, MDX)

Hi,

We have a requirement in which data for a member if not avbl. need to be replaced by the latest data avbl. in the last 'n' previous members and we need to control the number of previous members('n') that can be traversed.
ie..

if we are querying data for 1999 and we give traversal parameter of 3 yrs, in case of non avbl. data for 1999, the next avbl data between 1999-1997 should be placed there. In case of traversal year being 4 then the search range changes to 1999-1996.

Year--- DataSet1 --- DataSet2
1996--- 1 ------ 1
1997--- N/a ------ 2
1998--- N/a ------ N/a
1999--- N/a ------ N/a

Result Set --- YEAR --- (Traverse 3years) ---(Traverse 4years)

Data Set 1 --- 1999 --- N/a --- ------ 1
Data Set 2 --- 1999 --- 2 --- ------ 2

We used the fallowing query...but it is throwing the recursion error.

With Member [Measures].[Last Updated]AS 'tail(
Filter ( LastPeriods(3,[Time].Currentmember),
Not isEmpty([Measures].[Observation Value])
),1).item(0)'

Member [Measures].[Last Updated1]AS'
iif (NOt isEmpty([Measures].[Observation Value]),[Measures].[Observation Value],([Measures].[Last Updated]))'

Select
Crossjoin ({[Series].[All Series].[Current account].[BM.GSR.FCTY.CD],[Series].[All Series].[Travel & tourism].[ST.INT.XPND.MP.ZS]},{[Measures].[Observation Value],[Measures].[Last Updated1]}) on columns,
({[Time].[All Time].[1996],[Time].[All Time].[1997],[Time].[All Time].[1998],[Time].[All Time].[1999]}) on rows
from Test1
where([Country].[All Country].[IND])

Any help is highly appreciated.

Thanks and regards

RajaWhat is N/A? Null?

Also, you're trying to mix and match rows...do you want the MAX?|||Thaks for responding..

1. N/A is Not Available. It means for these combination of dimensions there is no data.

2. I am writing a query like, for each member what is the value available in cube and what value it has been replaced with (in case of n/a cases).

Originally posted by Brett Kaiser
What is N/A? Null?

Also, you're trying to mix and match rows...do you want the MAX?