Hi,
I'm new to the MDX query. I've built below MDX query to pull a MTD,
YTD, LYTD FSR_CASES. I've created the members "FSR_CASES_MTD",
"FSR_CASES_YTD" in "FSR_PROD_CUBE". The goal is, I want to pull the
year todate, last year todate, this year month todate, and last year
month todate of "FSR_CASES" from "FSR_PROD_CUBE". The problem is, I
don't know what's the max date in the dimension "TIME_DIM", in below
query, I have to specify the max date myself which is "20041021". Is
there a better way to do this?


WITH
MEMBER [Measures].[OCT-2003 MTD] AS '([Measures].[FSR_CASES_MTD],
[TIME_DIM].[20031021])'
MEMBER [Measures].[OCT-2004 MTD] AS '([Measures].[FSR_CASES_MTD],
[TIME_DIM].[20041021])'
MEMBER [Measures].[CM%] AS 'iif(isEmpty([Measures].[OCT-2003
MTD]),0,([Measures].[OCT-2004 MTD]-[Measures].[OCT-2003
MTD])*100/[Measures].[OCT-2003 MTD])'
MEMBER [Measures].[2003 YTD] AS '([Measures].[FSR_CASES_YTD],
[TIME_DIM].[20031021])'
MEMBER [Measures].[2004 YTD] AS '([Measures].[FSR_CASES_YTD],
[TIME_DIM].[20041021])'
MEMBER [Measures].[YTD%] AS 'iif(isEmpty([Measures].[2003
YTD]),0,([Measures].[2004 YTD]-[Measures].[2003
YTD])*100/[Measures].[2003 YTD])'

select {[Measures].[OCT-2003 MTD] , [Measures].[OCT-2004 MTD]
,[Measures].[CM%], [Measures].[2003 YTD] , [Measures].[2004 YTD]
,[Measures].[YTD%] } on columns, NON EMPTY
{Descendants([BROKERHIERARCHY].[0001], [AREADIRECTORID])} DIMENSION
PROPERTIES [BROKERHIERARCHY].[AREADIRECTORID].[DisplayName] on rows
from FSR_PROD_CUBE