SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: I would like to create MDX calculated measure that instead of summing children amounts,uses last child amount

A: Normally best way to create this in SSAS 2005 is to create real measure with aggregation function LastChild. If for some reason you still need to create calculated measure, just use fuction .LastChild on current member of Date dimension, and you will allways get value of last period child.

Example: We want to see last semester value for year level data. Lets first see what data values are at Calendar Semester level:

SELECT {[Measures].[Internet Order Count]} ON 0
, DESCENDANTS([Date].[Calendar].[All Periods],[Date].[Calendar].[Calendar Semester] ) ON 1
FROM [Adventure Works]

Result:

Internet Order Count
H2 CY 2001 1,013
H1 CY 2002 1,193
H2 CY 2002 1,484
H1 CY 2003 1,738
H2 CY 2003 9,181
H1 CY 2004 12,074
H2 CY 2004 976

Now lets create calculated measure that instead of aggregating values from the level below, uses last child value:

WITH MEMBER [Measures].[Internet Order Count For End of Period Below] AS
([Date].[Calendar].CurrentMember.LastChild, [Measures].[Internet Order Count])
SELECT [Measures].[Internet Order Count For End of Period Below] ON 0
, [Date].[Calendar].Children ON 1
FROM [Adventure Works]

Result:

Internet Order Count For End of Period Below
CY 2001 1013
CY 2002 1484
CY 2003 9181
CY 2004 976

As you see instead of seeing sum for semesters, you see last semesters value for each year.