[Cummulative] does not work

-- Take all year but current year

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[Year],

Ancestor([Time].[FiscalYear].CurrentMember, [Time].[FiscalYear].Year).PrevMember

)

, [Measures].[PTD Actual])

+

-- Take current YTD

SUM(Ytd([Time].[FiscalYear].CurrentMember), [Measures].[PTD Actual])

-

Above MDX is trying get a Cummulative number up to current time period. The time.FiscalYear was defined with Y, Q and M levels. I tried with an (All) level and w/o (All) level. But PeriodsToDate just does not go beyond pervious year to get all the Periods (here all the periods are all the years up to last year). So when I look at Y2007 cummulative number, I only see it adds up all the number in Y2006 and whatever periods up current in Y2007. Anything before 2006 was not included.

Is there a correct way to do cummulative actual (add up all the previous years plus all the period up to CurrentMember in current year)?

Try this:

SUM({NULL:[Time].[FiscalYear].CurrentMember}, [Measures].[PTD Actual])

|||

Hi, Deepak:

Could you explain a bit what the purpose of NULL in the function?

Thanks!

Julius

|||

Sorry, Deepak.

I am on AS 2000. It gives me syntax error on NULL.

Could you help?

Thanks again!

Julius

|||

The "NULL:" won't work in AS 2000, so (assuming that there is an "All" level) you could try:

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[All]

)

, [Measures].[PTD Actual])

|||

Deepak:

Interesting that AS2000 took this MDX.

But it seems the set is not the calc member is meant to be. By using [All] w/o defining a CurrentMember, it seems return the ENTIRE time dimension which includes the future period.

If we have 2003, 2004, 2005, 2006, 2007, 2008 as Yearl level members, how do I ask MDX to give the "Cumulative up to date" figure up to 2006 (including 2003, 2004, 2005, 2006) w/o anything from 2007 and 2008? I think (All) will just return entire dimension member set, right? Or in other words, how do I tell MDX that I only want to PeriodsToDate up to my current Year level member (or Previous Year level member, so I can add YTD accurately for current year) when using (All)?

I am confused.

Thanks much!

Julius

|||Depends on how you define your current date - if it's based on the latest date with data in the cube, then previous year (2006) could be computed like Tail(NonEmptyCrossJoin([Time].[Year].Members)).Item(0).PrevMember. Without explicitly selecting a CurrentMember, the DefaultMember will be used instead. So you could set the Time dimension's DefaultMember to the above expression, so that the year 2006 is selected by default.