MDX - SSAS, getting result count of last measurement within a period

• Jens Henrik

  Newbie

  Points: 9

  More actions

  March 6, 2013 at 1:42 am

  #271959

  Hi I am having trouble trying to write some MDX code.

  I am trying to get the last measurement result pr. person within a period. I have the following measurements and results:

  Person Measurement result Date

  1 1 overweight 2011-01-01

  1 2 Normal 2011-03-01

  2 3 Underweight 2011-01-01

  2 4 Underweight 2011-02-01

  2 5 Normal 2011-03-01

  1 6 overweight 2012-01-01

  Right now I am only able to get a normal count on measurements and distinct count on persons. So I can create a table like this (Full period):

  Number of persons Number of measurements

  Overweight 1 1

  Normal 2 3

  Underweight 1 2

  But I would like to have only the last category within my chosen period, so it looks like this (Full period):

  Number of persons Number of measurements

  Overweight 1 1

  Normal 1 1

  Underweight 0 0

  But it should also be able to create a table like this for the period (year 2011)

  Number of persons Number of measurements

  Overweight 0 0

  Normal 2 2

  Underweight 0 0

  My dimensions and measurements are like the following:

  [Measures].[Numb persons]

  [Measures].[Numb measurements]

  [Registrationdate].[Date]

  [Person].[ID]

  [result].[Name]

  I hope someone can help me

  thanks in advance

  Br

  Jens H