Using PeriodsToDate Function

[BKhanna]

I have a cube with several measures. I need to be able to calculate a period to date number across all measures for any selected time dimension member. Is it possible to do this without creating a separate PTD calculated measure for each measure?

 

[justins57]

BK There is a PeriodsToDate function in MDX that you can use. Examples:

Syntax
PeriodsToDate([«Level»[, «Member»]])

PeriodsToDate(Quarter,[05-Sep-1997]) Returns the set of days from the beginning of Quarter3.

PeriodsToDate(Year, March) Returns the set {January, February, March}.

PeriodsToDate(Year) Returns the set of members from the beginning of the year that is the ancestor of Time.CurrentMember, through Time.CurrentMember.

PeriodsToDate() Returns the set of members from the beginning of the level containing the period of Time.CurrentMember to Time.CurrentMember. All the returned members are at the same level as Time.CurrentMember

 

[BKhanna]

Justin, thanks for your reply. I tried creating a calculated member in the measures dimension, and typed PeriodsToDate() in the Value Expression box. It gave me an error "Formula error - cannot convert set to expression-in the PeriodsToDate Function". I am not sure what to put in the value expression? What I want to do is to create calculated members for YTD, QTD and MTD across all measures as in the example below:

Jan_01 Jan_02 Jan_03 MTD
Measure1 100 110 120 110 (average)
Measure2 30 30 40 100 (sum)

Thanks for your help!

 

[BKhanna]

I think I answered my own question. The PeriodsToDate function only returns a set of periods. Thanks anyway!

 

[justins57]

BK,

Here is a sample query that might help. Run it against the Foodmart2000 database.

with

set quarters as '[Time].[1997].children'
set firstmonthofqtr as 'Generate(quarters,{[Time].CurrentMember.firstchild})'
member [Time].[Avg / Month] as 'Avg(firstmonthofqtr)'
member [Time].[Total] as 'Sum(firstmonthofqtr)'
select

{firstmonthofqtr,[Total],[Avg / Month]} on columns

from sales

hth,

Justin