4

I have a Measure - 'TEST' which is SUM across all the Dimensions except across DATE Dimension for which we need the value on the Last Day.

I created the Measure with an aggregate as 'SUM' and created a Scope statement to handle the exception

 SCOPE([Date].[Date].[All])
   THIS = TAIL([Date].[Date].MEMBERS,1).ITEM(0) ;
 END SCOPE

Its working well and picking up the Last day's value. But the problem is its always picking up the Last day's value and doesn't respect the Filters on the report.

For eg. If I have

Jan 1 - 100 ,
Jan 2 - 200 ,
Jan 3 - 300

When I get a report for Jan 1 and Jan 2 --- I do expect to see 200. Instead i get 300 with the above MDX.

Thanks. any help is much appreciated.

Talasila
  • 161
  • 6
  • I think that is because your scope statement told it to use all dates rather than those filtered by your query. – mmarie Sep 17 '13 at 19:54
  • Are you sure this scope statement is the culprit? Is the behavior the same if you comment it out? Is there another place where the context might be changed in your query or the calculation script? – FrankPl Sep 18 '13 at 15:35

1 Answers1

1

You could try to add EXISTING to your expression. This evaluates the members currently available due to filters or row or column headers. Your expression would then be 

TAIL(EXISTING [Date].[Date].MEMBERS,1).ITEM(0) ;
FrankPl
  • 13,205
  • 2
  • 14
  • 40