ssas - Aggregation of an MDX calculated measure when multiple time periods are selected -

ssas - Aggregation of an MDX calculated measure when multiple time periods are selected -

in ssas cube, have several measures defined in mdx work fine except in 1 type of aggregation across time periods. don't aggregate (and aren't meant to) 1 aggregate gives wrong answers. can see why, not prevent it.

the total highlighted in excel screenshot below (damn, not allowed include image, reverting old-fashion table) simplest case of goes wrong. in example, 23,621 not grand total of 5,713 , 6,837.

active commitments acquisitions net lost commitments growth in commitments 2009 88,526 13,185 5,713 7,472 2010 92,125 10,436 6,837 3,599 total 23,621 23,621 active commitments works fine. calculated point in time , should not aggregated across time periods. acquisitions works fine. [measures].[growth in commitments] = ([measures].[active commitments],[date dimension].[fiscal year hierarchy].currentmember) - ([measures].[active commitments],[date dimension].[fiscal year hierarchy].prevmember) [measures].[net lost commitments] = ([measures].[acquisitions] - [measures].[growth in commitments])

what's happening in screenshot total of net lost commitments calculated total of acquisitions (23,621) minus total of growth in commitments (which null).

aggregation of net lost commitments makes sense , works non-time dimensions. want show null when multiple time periods selected rather erroneous value. note not same disabling aggregation on time dimension. aggregation of net lost commitment works fine time hierarchy -- screenshot shows right values 2009 , 2010, , if expand quarters or months still right values. when multiple time periods selected aggregation fails.

so question how alter definition of net lost commitments not aggregate when multiple time periods selected, continues aggregate across other dimensions? instance, there way of writing in mdx:

create fellow member currentcube.[measures].[net lost commitments] (iif([date dimension].[fiscal year hierarchy].**multiplemembersselected** , null , [measures].[acquisitions] - [measures].[growth in commitments]))

advthanksance,

matt.

a suggestion source has solved me. can utilize --

iif(iserror([date dimension].[fiscal year hierarchy].currentmember), , null , [measures].[acquisitions] - [measures].[growth in commitments]))

currentmember homecoming error when multiple members have been selected.

ssas mdx