MDX Statement and Calculated Measure showing different outcomes

• giles.clapham

  SSCrazy

  Points: 2204

  More actions

  November 22, 2012 at 7:38 am

  #150186

  Hi,

  I wrote a series of MDX statements to find a total figure. I was happy it work regardless of what filters were being applies and what was on the Column and Row axis.

  the formula was:

  With

  MEMBER [Measures].[_ReferencePeriodTotal]

  as

  'Sum([Advance Period].Period.FirstSibling:[Advance Period].Period.LastSibling,[Measures].[ReferencePeriod])'

  However when I then went into Business Inteligence Design Studio and created it as calculation called ReferencePeriodTotal (Note no underscore) in the Measures dimension using

  Sum([Advance Period].Period.FirstSibling:[Advance Period].Period.LastSibling,[Measures].[ReferencePeriod])

  It seems to work for the past but for future dates if i put [Advance Period].Period.AllMemeber in the Row axis it seems to be calcualting it differently.

  I've even put the two calulations in the same MDX query window and the MDX Member works as expected and the Calculated one seems to be showing just the total for the day.

  I really don't know what the difference is between the two ways of creating the calcualtion so any guidance to aid my investigation is really very welcome.

  Thanks

  Giles

• giles.clapham

  SSCrazy

  Points: 2204

  More actions

  November 23, 2012 at 6:25 am

  #1561856

  I rewrote the calculation to be a tuple

  ([Advance Period].Period.[All],[Measures].[ReferencePeriod])

  and it was consistent between XML and the Calculation.

  So I've got my result but it would be interesting to know what the difference was originally.