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)
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.