Getting Fiscal year using Case statement in MDX Query gives Error

Question

I am using MDX query in Excel to get the data from the cube. And every July the Fiscal year gets changed and we have to change manually. Is there any way I can implement this case statement in following query?

SELECT 
  ( { [Dim ID].[ID Key].&[7]} ) ON COLUMNS 
FROM ( SELECT 
  ( { [Billing].[FY].&***[2015]*** } ) ON COLUMNS
  FROM [Billing DW])
) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Instead of year [2015] I want to write this case statement.

CASE 
  WHEN MONTH(getdate()) > 7 THEN YEAR(getdate()) + 1 
  ELSE YEAR(getdate()) 
END ;

Answer 1

You need to do some string manipulation and VB code to achieve this since MDX doesn't have a getdate() function. As whytheq mentioned though, you will save yourself a lot of headache in the future to just include Fiscal Year on the dimension itself.

 SELECT ( { [Billing].[FY].&[2015] } ) 

would become

 SELECT CASE WHEN FORMAT(now(), "MM") > "07"
    THEN StrToMember("[Billing].[FY].&["+Format(now(), "yyyy")+"]").lead(1)
    ELSE
    StrToMember("[Billing].[FY].&["+Format(now(), "yyyy")+"]") END