Greetings,

I am a OLAP beginner and having trouble. Can someone give me a few
tips please.


I have a Financial date dimension with the following levels in the
hierarchy.

[Date].[Financial]
[Date].[Financial].[FYear]
[Date].[Financial].[FQtr]
[Date].[Financial].[FWeek]
[Date].[Financial].[FDate]
e.g. [Date].[Financial].[All
Date].[2004].[FQ1/04].[FSep/04].[FW8/04/Sep 15].[18/09/03] for
18-Sept-03


The Financial Year start at 1-Aug and Financial Months are based round
5/4/4 periods.

FDate has a property "DayNumberOfWeek".

I am trying to produce a report that looks as follows:


Last Complete Week MonthToDate QuarterToDate
YearToDate
Sales Budget S/B % Sales Budget S/B % Sales Budget S/B % Sales
Budget S/B %


I can find the last date with sales using

(Tail ( Filter ( {[Date].[Financial].[29/07/02]:[Date].[Financial].[01/11/04]}
, NOT ISEMPTY([Measures].[Qty]) ) ,1).item(0))

but I can't get the "DayNumberOfWeek" of FDate out of the last date
with any sales.

As the report is only to be updated when weeks are complete, the
MonthToDate, QuarterToDate, YearToDate are the values as at the end of
the last complete week.
The last complete week may not be the parent of the last FDate with
sales. The last complete week may have different parents of the last
sale date at period boundaries.

The rules are:
If ("DayNumberOfWeek" of last date with sales = 7) then I want to use
the parents of the last FDate with Sales but
If ("DayNumberOfWeek" of last date with sales <> 7) then I want to
use the parents of ( FDate lagged by "DayNumberOfWeek")


Any suggestions?

Regards Noel