Im using Microsoft Analysis Services and have build an OLAP Cube with
several dimension and measures and a time dimension.

Ive added a calculated member to calculate the year to date values
using the
following code.

sum(periodstodate([Date].Year),[Measures].[Hours])

This works fine but what i really want is only sum up the values back
to the 1st of May, figures before the first of may should be shown in
the previous
year to date figure.

What I want is something like this....

2003 2003 2003 2003 2003 2003 2003 2003 2003
Jan feb Mar Apr May Jun Jul Aug Sep

Hours 5 4 3 6 8 2 4 1 8
Year2Date 19 23 26 32 8 10 14 15 23


What Im getting is .....

2003 2003 2003 2003 2003 2003 2003 2003 2003
Jan feb Mar Apr May Jun Jul Aug Sep

hours 5 4 3 6 8 2 4 1 8
Year2Date 5 9 12 18 26 28 32 33 41


Has anyone any pointers on how i can change my calculated member.

Thanks
John.

If you simply change the start date of your date dimension to the 1st
of May then you should be able to use the calculated member as is and
get the expected result.

If you need to have a fical calendar and a real one then go for 2
seperate dimensions with different depths if needs be.

Hope this helps.

Mark Scanlon
XLCubed

The display must be on calander year and the YTD must start at 1st May.

Ive tried having two dimensions, but the problem with this is that
the users have to drag both dimensions onto the piviot control and
make sure they select the same years etc... from both.

I was hoping to use something like LastPeriods() or .lag but I
could not figure out how many periods to count back to get back
to 1st of May.

Thanks,
John.

mark.scanlon (AT) xlcubed (DOT) com (MPS) wrote in message news:<f619073b.0307310626.40e0dbf4 (AT) posting (DOT) google.com>...