Results 1 to 3 of 3

Thread: MDX Query: Every end of each month on rows

  1. #1
    Join Date
    Sep 2011
    Posts
    1

    MDX Query: Every end of each month on rows

    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,

    Code:
    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

  2. #2
    Join Date
    Nov 2011
    Posts
    1
    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]

  3. #3
    Join Date
    Oct 2013
    Posts
    1
    If hierarchy is ...-Months-Days:

    Code:
    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:

    Code:
    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]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •