MDX Query: Every end of each month on rows

[TitoPeru]

Hi all, I'm new to MDX,
I want to create a query to get on Rows every end of each month.

The basic query on AW gets all days,

SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { [Date].[Calendar].[Date]  } ON ROWS
FROM [Adventure Works]

But I want to get only month's ends:


.................... Sales Amount
July 31, 2005....... 15,012.18 $
August 31, 2005..... 20,859.78 $
September 30, 2005.. 35,782.70 $
October 31, 2005.... 6,749.98 $
November 30, 2005... 18,590.45 $
December 31, 2005... 22,168.72 $


Thanks in advance
Tito

[GDub]

How about:

WITH MEMBER [Measures].[LastDayOfMonth] AS
[Date].[Calendar].currentMember.Lastchild.Name
SELECT NON EMPTY { [Measures].[LastDayOfMonth]
, [Measures].[Sales Amount]}
ON COLUMNS
, NON EMPTY { ([Date].[Calendar].[Month].ALLMEMBERS ) }
ON ROWS
FROM [Adventure Works]

[Paya007]

If hierarchy is ...-Months-Days:

SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { filter([Date].[Calendar].[Date],  [Date].[Calendar].currentmember is [Date].[Calendar].currentmember.parent.lastchild) } ON ROWS
FROM [Adventure Works]

If hierarchy is ...-Months-...-Days:

NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { filter([Date].[Calendar].[Date],  [Date].[Calendar].currentmember is closingperiod([Date].[Calendar].[Date],  [Date].[Calendar].currentmember.parent.parent.....)) } ON ROWS
FROM [Adventure Works]