Hi,

I am trying to create a calculated member field which
calculates the Fiscal YTD Total for the current year. Can
someone help me with how the formula should look for a
member like this?

I have a dimension called Period, which has one level
called Gltransperiod - which is made up of periods (our
fiscal year starts in April). The periods look like:

2004007 - October 2003
2004008 - November 2003
2004009 - December 2003
and so on...

This dimension is related to the gltransperiod field in
the fact table. The measure is Glusdbal.

Thank you very much!

Trevor

Hi Trevor,

Assuming that you just have one measure:
Glusdbal, which aggregates by summing,
then here is how a calculated measure,
FYTDusdbal, could be created in MDX:

'Sum(LastPeriods(StrToValue(Right(
[Period].CurrentMember.Name,2)),
[Period].CurrentMember),
[Measures].[Glusdbal])'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thank you Deepak!

It works!

Deepak,

One problem...when I add the new calculated field to my
pivot table in Excel 2000, it crashes Excel. It works fine
in the cube browser in Analysis Services though, any ideas?
It does give an ERR when the All Period level is
chosen...maybe restricting this with an Iif statement?

Unsure,
Trevor

Trevor,

You're right, conditional logic is required to make the formula work at
the 'All' level - don't know if that's causing Excel to crash. You can
try something like:

'iif([Period].CurrentMember.Level is
[Period].[Gltransperiod],
Sum(LastPeriods(StrToValue(Right(
[Period].CurrentMember.Name,2)),
[Period].CurrentMember),
[Measures].[Glusdbal]),
[Measures].[Glusdbal])'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!