MDX Sum Over multi selection in a time dimension

• maretix

  Hall of Fame

  Points: 3835

  More actions

  March 30, 2014 at 5:01 pm

  #287053

  Good evening to all.

  I try to have an help it is possibile

  My name is Mauro i am writing from Italy...and i am little new about MDX SSAS 2008 R2.

  I have a SALES Cube that contains a [Measures].[Sales Amount] and a time dimension [Calendar].

  Time dimension CALENDAR is like this :

  DIMENSION [Calendar]

  HIERARCHY [DataAAAAMMGG]

  First level of Hierarchy [Year]

  Second level of hierarchy [Month]

  Third Level of hierarchy [Day]

  I use Excel 2013 to browse SALES Cube.

  I select Calendar Dimension in a multi value mode .

  That is:

  1) I both select Year=2014 Month = 3 Days from 1 to 28 (i want sales amount of 1-28 OF march 2014)

  AND

  2) I both select Year=2014 Month = 3 Days from 2 to 29 (i want sales amount of 1-28 OF march 2014

  So i choose different periods in time dimension , and i want to calculate difference of sales between first selection 1) and second selection 2)

  Example: (Sales 2014-3- from 1 to 28 ) - (Sales 2013-3 From 2 to 29)

  I do not know if i did good to use parallel period...

  I tried but i do not what is the best to do that...

  If i drag [Measure].[Sales] it works but i need to calculate a delta from sales of different years , same month , and different range of days...as described before.

  I wrote a calculate member but it calculate very wrong values...:

  'SUM

  (Existing [Calendar].[DataAAAAMMGG].[Month].Members,

  ([Measures].[Sales] - (ParallelPeriod([Calendar].[DataAAAAMMGG].[Year], 1), [Measures].[Sales])))'

  I hope someone help me...

  I am sure someone did something of similar in his own cubes..

  To be clear if Sales Amount of 2014-3- from 1 to 28 = 100.000 euro and Sales Amount of 2013-3- from 2 to 29 = 80.000

  i need to have difference 100.000 - 80.000 = 20.000

  ps. Notice i select multivalues in time dimension and not the same...that is days range different (1-28 and 2-29)

  I hope you can help me thanks.

  Regards.