MDX filter last periods where Month exists

• gwhelan87

  Newbie

  Points: 9

  More actions

  June 11, 2013 at 8:54 pm

  #97157

  Hi

  I have been trying to work out a solution to this problem for a couple of days now but I am relatively new to Analysis Services.

  I have a query that selects the number of responses (measure), ports (dimension) for the last 2 month periods (dimension)

  It is acceptable if there is no data for previous month

  If there is no data for the most recent month then the port is to be ignored

  Here is my query which works for some ports and not others

  SELECT

  NON EMPTY {

  [Measures].[Responses]

  }

  ON COLUMNS,

  NON EMPTY

  FILTER (

  {

  [Port].[Port].[Port] * LASTPERIODS(2, STRTOMEMBER("[Time].[Month].&[2013-05-01T00:00:00]"))

  },

  STRTOMEMBER("[Time].[Month].&[2013-05-01T00:00:00]"))

  ON ROWS

  FROM [Cube]

  I'm pretty sure my logic isn't correct but I've been trying out using EXISTS, sub queries, WHERE clauses and this is the closest I have came to a resolution.

  Thanks in advance Graham