Date value associated with a max value of a measure at the lower level.

• JayKur

  SSC Journeyman

  Points: 79

  More actions

  October 21, 2014 at 2:20 pm

  #151384

  Hi,

  Looking for an mdx statement to get a member value of a Hierarchy corresponding to the MAX value of a measure. For example My date hierarch is Year - Month - Date.

  My measure is x.

  So when I am looking at the data on a Month level I need to display the date value where x is maximum.

  The below expression will give me the max date value associated with a measure at that level.

  TAIL( NONEMPTY( [Date].[Date].[Date] ,[Measures].[x]),1).Item(0).MemberValue

  But I need the date value associated with a max value of a measure at the lower level.

  Any response will be greatly appreciated.

  Thanks

  Jay

• Ashok Teotia

  SSCrazy

  Points: 2407

  More actions

  November 13, 2014 at 4:24 am

  #1758174

  You can try this:- Its perfect, you will just love it.

  WITH

  MEMBER Measures.MaxMonthValue AS

  Max

  (

  Descendants

  (

  [Date].[Calendar].CurrentMember

  ,[Date].[Calendar].[Date]

  ,SELF

  )

  ,[Measures].[Internet Sales Amount]

  )

  MEMBER Measures.MaxMonthDate AS

  TopCount

  (

  Descendants

  (

  [Date].[Calendar].CurrentMember

  ,[Date].[Calendar].[Date]

  ,SELF

  )

  ,1

  ,[Measures].[Internet Sales Amount]

  ).Item(0).Name

  SELECT

  {

  Measures.MaxMonthValue

  ,Measures.MaxMonthDate

  ,[Measures].[Internet Sales Amount]

  } ON COLUMNS

  ,Descendants

  (

  [Date].[Calendar].[Calendar Year].&[2007]

  ,[Date].[Calendar].[Month]

  ,SELF

  ) ON ROWS

  FROM [Adventure Works];