Hi,

We have a cube where we will show the last date of teh month as the
total of a month and the average of all dates in a month as the result
for a month. So if you look at 15 feb you should see data based on data
between 15-feb 2005 and 2006.

We will use a Time slicer as page filter and have always any choice
(Year or Month or Date) show the correct data.

What is the mdx code to show always the correct data if you choice for
example 2006, feb 2006 or even 15 feb 2006.

Regards, Marco

use "LastPeriods" MDX function
AVG or Sum whatever it is

AVG(Lastperiods(12,Time.currentmember),[Measures].[sales])


regards,
Pras

use "LastPeriods" MDX function
AVG or Sum whatever it is

AVG(Lastperiods(12,Time.currentmember),[Measures].[sales])


regards,
Pras

Hi,

Thank you this is working for if you choice for example a month but
what would be the MDX code if you choice for example a date 1 feb 2006
and you would like to show always the average of the last 12 periods (=
months).

Regards, Marco

As I mentioned off-line (I thought I would answer here too incase
someone else is watching this thread), you could do something like:

[Month]).PrevMember),[Measures].[Sales])
If you are on the month level, it will return the current month. If you
are lower than month it will work it's way up the heirarchy to the month
level.

You may want different logic if the user picks a level higher than
month, you could handle that with an IIF() function.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1142501063.927103.110050 (AT) v46g2000cwv (DOT) googlegroups.com>,
olap (AT) gmsbv (DOT) nl says...