Help required with PERIODSTODATE function that stops at CURRENT DATE!

[Nassy]

Hi

I am trying to create an MDX query which will return the CURRENT YEAR year to date revenue value.

I have spent ages with this and have so far ended up with the following:



WITH MEMBER Measures.[Current YTD Revenue] AS



Aggregate(PeriodsToDate([Date].[Fiscal Calendar].[Fiscal Year],
StrToMember("([Date].[Fiscal Calendar].[Fiscal Year].&["+
IIF(MONTH(NOW()) >= 4, (STR(FORMAT(NOW(), "yy")) + "/" + STR(FORMAT(DATEADD("YYYY",1,NOW()), "yy"))), (STR(FORMAT(DATEADD("YYYY", -1,NOW()), "yy")) + "/" + STR(FORMAT(NOW()), "yy"))
)

+"].&[Q" + STR(CASE WHEN MONTH(NOW()) >= 1 AND MONTH(NOW()) <= 3 THEN 4
WHEN MONTH(NOW()) >= 4 AND MONTH(NOW()) <= 6 THEN 1
WHEN MONTH(NOW()) >= 7 AND MONTH(NOW()) <= 9 THEN 2
WHEN MONTH(NOW()) >= 10 AND MONTH(NOW()) <= 12 THEN 3
END)
+ "].&[" + FORMAT(NOW(), "MMMM") + "].&[" + Format(NOW() ,"MMM") + IIF(DAY(NOW()) <=9," ", " ") + STR(DATEPART("d", NOW())) + " " + STR(DATEPART("yyyy",NOW()))+"])"))
this awful looking code just returns today's date !!
,[Measures].[Revenue])

Select
{[Measures].[Revenue], [Current YTD Revenue]} ON Rows,
[Current Year] named set returning current year ON Columns
FROM [Cube]



However this doesn't appear to work properly as if I take out the named set [Current Year] and instead show the new measure against Fiscal Year members I get the same YTD value for each Fiscal Year. This can't be right.

All I want to do is to create a YTD total starting from the current financial year...

Help!! Finding this MDX malarky excruciatingly hard!

Nassy