Timephased OLAP cube and missing data search

[orangebloss]

Hi

I have a pivot based on a timephased OLAP pulling data from Project Server. The pivot shows the Baseline and Baseline 1 work for each week that exists and tables on another sheet show the current week and the status of the project against that baseline.

The issue I am having is where there is no baseline data for a given week so I want it to be able to search the pivot to find the last entry before the current week

I have used this formula in previous weeks reports which doesn't appear to be working

=(IF((VLOOKUP(Week27,PivotTable,Baseline,FALSE)),(VLOOKUP(Week27,PivotTable,Baseline,FALSE))/40,(INDEX(Baseline,MATCH(9.99999999999999E+307,Baseline)))))

Week	Actual Work	Baseline work	Baseline1 Work
Week1	64	64	70
Week3	120	120	100
Week4	292	276	276
Week16	1102	1232	1253
Week17	1327	1596	1500

The formula I want to be able to write is for example

Look for Week10 Baseline data
If no Week 10 Baseline data return the last Baseline data before today/current week
so using the table above it would return the baseline data for Week4

I have worked out how to minus the week by 1 using Right 2, which works when the data is available for the previous week but I need to be able to repeat it until it hits the correct week