MDX - dynamically selects the latest date from the dates selected in the report

Question

I have Measure - 'TEST', which is SUM in all dimensions except DATE Dimension, for which we need the value on the last day.

I created a 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 

Works well and collects the value "Last Day". But the problem is that it always gains the value "Last Day" and does not take into account filters in the report.

For example, if I have

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

When I get the report for January 1 and January 2, I expect to see 200. Instead, I get 300 with the above MDX.

Thanks. any help is much appreciated.

Answer 1

You can try adding EXISTING to your expression. This evaluates the members currently available due to filters or row or column headers. Then your expression will be

 TAIL(EXISTING [Date].[Date].MEMBERS,1).ITEM(0) ;