MDX during period calculation

• tarkol.rattanakitti

  Newbie

  Points: 5

  More actions

  November 10, 2011 at 8:18 pm

  #146623

  Hi All,

  I'm quite new to the MDX. I have a problem with the time dimension. I have a measure which is counting numbers of student enrolments. I want to know how many enrolments are in a period; Enrolment Start Date <= 31 Dec 2010 and Enrolment End Date >= 1 Jan 2010. To get the measure of the enrolment count I can do this;

  SELECT[Measures].[Enrolment Count] ON COLUMNS

  FROM[Student Enrolment]

  WHERE(NULL : [Enrolement Start Date].[Calendar Date].[Date].[2010-12-31 00:00:00.000], [Enrolment End Date].[Calendar Date].[Date].[2010-01-01 00:00:00.000] : NULL);

  But how can I include MonthName from the date dimension (ON ROWS) in my result so it looks like;

  Month Enrolment Count

  Jan 250

  Feb 550

  Mar 280

  .

  .

  .

  Dec 250

  Thanks

• mark 38539

  SSC Eights!

  Points: 848

  More actions

  November 11, 2011 at 8:22 pm

  #1406629

  Can you not just use a set?

  WITH SET [DateRange]

  AS

  ( Filter([Enrolement Start Date].[Calendar Date].[Date].Members, [Enrolement Start Date].[Calendar Date].[Date] <=[Enrolement Start Date].[Calendar Date].[Date].&[WhateverDateKey] AND [Enrolement Start Date].[Calendar Date].[Date] >= [Enrolement Start Date].[Calendar Date].[Date].&[WhateverDateKey])

  )

  SELECT [Measures].[Enrolment Count] ON COLUMNS,

  [DateRange] ON ROWS

  FROM [Student Enrolment]