I have a Time hierarchy (Year-Quarter-Month-Date) and also a "current date" attribute in the time dimension. I am looking to create a calculated member called MTD Sales where i should have the option to select Current date and MTD Sales  or Date from the time hierarchy and MTD Sales. This way the user has just one Measure field but has the flexibility to choose either the current date and get MTD as of current date  or any prior date from the time hierarchy so the MTD sales is as of that date. I am able to do it independently but having a hard time combining both the options in one calculated member. Did not have much luck with the IIF condition. Any input is greatly appreciated.

Hi Ambika,

According to your description, you want to calculated MTD based on current date or other date with only one measure. Right?

In Analysis Services, we can use SCOPE statement to specify different calculation based on different slicing members. So for your requirement, you can create calculated measure and us SCOPE statement like below:

CREATE MEMBER CURRENTCUBE.MEASURES.[x] AS NULL;
SCOPE(MEASURES.[x]);
    SCOPE([Date].[Date].MEMBERS);
        THIS = sum([Date].[Date].MEMBERS,[Measures].[MTD]);
    END SCOPE; 
    SCOPE( [Date].[Current Date] );
        THIS = sum([Date].[Current Date],[Measures].[MTD]);
    END SCOPE;     
END SCOPE;

Reference:
SCOPE Statement (MDX)

Regards,

Hi Simon - Thanks for your reply. I tried as you had suggested but i get null when i run this. 

CREATE MEMBER CURRENTCUBE.[Measures].[TestMTDGrossSales]
 AS NULL ; 

SCOPE(Measures.[TestMTDGrossSales]); 
 SCOPE([Time Dimension].[Time Hierarchy].MEMBERS); 
   THIS=sum([Time Dimension].[Time Hierarchy].MEMBERS,[Measures].[Gross Sales Amount].[MTD]); 
 END SCOPE; 
 SCOPE([Time Dimension].[CURRENT DT]); 
   THIS =sum([Time Dimension].[CURRENT DT],[Measures].[Gross Sales Amount].[MTD]); 
 END SCOPE; 
END SCOPE;

 I tried to tweak this, however it is not returning  Month to date sales  but  just gives the current day's sales. 

SCOPE(Measures.[TestMTDGrossSales]); 
 SCOPE([Time Dimension].[Time Hierarchy].currentMember); 
   THIS=sum(periodstodate([Time Dimension].[Time Hierarchy].[Month],[Time Dimension].[Time Hierarchy].currentmember),[Measures].[Gross Sales Amount]); 
        END SCOPE; 
 SCOPE([Time Dimension].[CURRENT DT]); 
   THIS =sum([Time Dimension].[CURRENT DT],[Measures].[Gross Sales Amount]); 
 END SCOPE; 
END SCOPE;

Any idea where i am making the mistake.