MDX formatting results

Question

I'm new to MDX querying. I am having trouble changing how the MDX output is formatted. I have made a similar example using the "Adventure Works Internet Sales Model". See below:

WITH 
  MEMBER [Measures].[Calculate YTD] AS 
    Sum
    (
      periodstodate([Date].[Calendar].[Year],[Date].[Calendar].CurrentMember)
     ,[Measures].[Internet Total Sales]
    ) 
SELECT 
  {[Measures].[Internet Total Sales]

   ,[Measures].[Calculate YTD]

  } ON COLUMNS,
  [Date].[Calendar].[Month] ON ROWS

FROM [Adventure Works Internet Sales Model]

WHERE ([Date].[Date].&[2012-01-01T00:00:00]:[Date].[Date].&[2018-01-01T00:00:00])WHERE ([Date].[Date].&[2012-01-01T00:00:00]:[Date].[Date].&[2018-01-01T00:00:00])

the results looks like this:

MDX results

What I would like to see is that the "[Date].[Calendar].[Month]" row to be displayed as the end of month date (e.g. 31-Mar-2019)

Answer 1

Welcome to SO!

You could add in an additional column showing the last day of each month:

WITH 
  MEMBER [Measures].[Last Day of Month] AS 
    [Date].[Calendar].CurrentMember.LastChild.MEMBER_CAPTION
  MEMBER [Measures].[Last Day of Month v2] AS 
    TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0).ITEM(0).MEMBER_CAPTION
MEMBER [Measures].[Calculate YTD] AS 
    Sum
    (
      periodstodate(
        [Date].[Calendar].[Year],[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Internet Total Sales]
    ) 
SELECT 
  { 
   [Measures].[Last Day of Month]
  , [Measures].[Last Day of Month v2]
   ,[Measures].[Internet Total Sales]
   ,[Measures].[Calculate YTD]
  } ON COLUMNS,
  [Date].[Calendar].[Month] 
  ON ROWS
FROM [Adventure Works Internet Sales Model]
WHERE 
(
 [Date].[Date].&[2012-01-01T00:00:00]:
 [Date].[Date].&[2018-01-01T00:00:00]
);

Output