Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Analysis Services 2005 MDX: Year End calculation

Analysis Services 2005 MDX: Year End calculation

Analysis Services 2005 MDX: Year End calculation

I'm having problems trying to get the YTD value for December only which needs to apply to all levels in the Calendar hierarchy.
My calculation for YTD uses the Time calculations concept for handling YTD, Prior Year etc.

    ([Time Calculations].[YTD]=
            CrossJoin({[Time Calculations].[Current Period]},
                PeriodsToDate([Time].[Calendar Hierarchy].[Year],
                [Time].[Calendar Hierarchy].CurrentMember)

The YTD calculation is correct.DecemberYTD works if I
hardcode it using the December 2008 member

     -- December YTD CALCULATIONS
([Time Calculations].[DecYTD]=
        ([Time Calculations].[YTD],[Time].[Calendar Hierarchy].[Year].&[2008].&[Quarter 4].&[13]));

I've also tried this but it doesn't apply the value to all levels of the hierarchy:

     -- December YTD CALCULATIONS

    Scope([Time].[Calendar Hierarchy].Members, [Time Calculations].[DecYTD], [Measures].[Amount]);

    This =
        ([Time Calculations].[YTD], ClosingPeriod( [Time].[Calendar Hierarchy].[Year],[Time].[Calendar Hierarchy].CurrentMember));

    End Scope;

Hope this makes sense.

Would anyone have a suggestion?
Thanks in advance.


RE: Analysis Services 2005 MDX: Year End calculation

Hi again,

I'm not sure if my previous message didn't make sense to anyone as I haven't had any responses. So to put it simply I would like to know what the MDX would be to get the YTD amount for December without having to hardcode a specific year and month.

Any help would be much appreciated!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close