MDX PeriodsToDate() w/dynamic parameters

[fieldian]

Hi All,

I'm trying to write a formula (actually, four) which will be used in Panorama NovaView to allow a user to see Revenue (on rows) by Previous Month (Current Year), Previous Month (Previous Year), Current YTD (ending at month in question), Previous YTD (ending at month in question) (on columns -- not necessarily in that order).

Basically, when run anytime during November 2005, the output would look like:

2004 Total | Oct 2004 | 2005 Total | Oct 2005
$ 100,000 | 15,000 | 120,000 | 17,500

The Dimension I'm working with is [Calendar Year], with levels: (All), [Calendar Year], [Calendar Month], [Calendar Week].

I've been focusing on the formula for the first column, since it is the most complex (I think). Here's the jist of what I am trying to do:

SELECT Measures.Revenue ON ROWS,
<<Last Year>>.January : <<Last Year>>.<<LastMonth>> ON COLUMNS
FROM SalesCube

I believe some combination of PeriodsToDate(), LastPeriod() and/or <<Current Year>>.PrevMember & <<Current Month>>.PrevMember is what I need, but I just can't get the syntax right.

Any help would be appreciated.

Thanks,
Ian Field
SD Union-Trib

[fieldian]

Sorry, should have posted this in the Analysis Services forum. Excuse the double post.