locked
YTD / PeriodsToDate Function with Need to Remove / Filter Periods RRS feed

  • Question

  • The scenario I’m coming across is not at all uncommon in enterprise financials – there are not only periods 1-12, but an additional three periods, 998, 999, and 0.  As many of you probably know, the first two are for “extra-year adjustments,” etc., where entries are made to impact overall year balances, generally, versus being posted to specific, operational time periods (months 1-12).  Moreover, period 0 is for opening balances. 

    The issues I’m running across are the use of the MDX Time functions (PERIODSTODATE() and, especially, the shorthand versions QTD(), MTD() and other xTDs() ), which, with a simple “periods 1-12” work great in their “relative” accumulation capabilities.  What I’m trying to figure out is the best “quick and dirty” way of adjusting the model, or even creating special calculated members, to grab 1-12 (or, in one case, 0-12) while leaving out 998 and 998 in the calculations – but, at the same time,  keeping 998 and 999 in the cube for a certain group of analysts that will need to occasionally access the balances they contain.

    Any tips appreciated ...

    Wednesday, March 17, 2010 10:14 PM

Answers

  • Does Quarter "0" need to be at the beginning - putting it at the end would reduce the complexity of PeriodsToDate() expressions?

    Otherwise, YTD() could be implemented something like:

    Aggregate(Generate({[Time].[Hierarchy].CurrentMember} as CurrentPeriod,
    Generate({OpeningPeriod([Time].[Hierarchy].[Quarter],
    Ancestor(CurrentPeriod.Item(0), [Time].[Hierarchy].[Year]))} as Quarter0,
    {OpeningPeriod([Time].[Hierarchy].[Period], Quarter0.Item(0)),
    Except(YTD(CurrentPeriod.Item(0)),
    Descendants(Quarter0, CurrentPeriod.Item(0).Level))})))


    - Deepak
    • Marked as answer by Raymond-Lee Friday, March 26, 2010 7:25 AM
    Friday, March 19, 2010 3:40 AM

All replies

  • What is the ordering of the periods (1-12 vs. 998, 999, 0) in your Time dimension? If the last 3 appear (in that order) at the end of the parent year (presumably in a separate quarter), then a YTD calculation could be defined like:

    Aggregate({ClosingPeriod([Time].[Hierarchy].[Period],
    Ancestor([Time].[Hierarchy].CurrentMember,  [Time].[Hierarchy].[Year])),
    YTD()})
    - Deepak
    Thursday, March 18, 2010 3:49 AM
  • The order is actually 0, 998 and 999 in front, in a "0" calendar quarter, follwed by quarter 1 (containing periods 1,2,3), quarter 2 (3,4,5) and so on.
    Thursday, March 18, 2010 1:50 PM
  • Does Quarter "0" need to be at the beginning - putting it at the end would reduce the complexity of PeriodsToDate() expressions?

    Otherwise, YTD() could be implemented something like:

    Aggregate(Generate({[Time].[Hierarchy].CurrentMember} as CurrentPeriod,
    Generate({OpeningPeriod([Time].[Hierarchy].[Quarter],
    Ancestor(CurrentPeriod.Item(0), [Time].[Hierarchy].[Year]))} as Quarter0,
    {OpeningPeriod([Time].[Hierarchy].[Period], Quarter0.Item(0)),
    Except(YTD(CurrentPeriod.Item(0)),
    Descendants(Quarter0, CurrentPeriod.Item(0).Level))})))


    - Deepak
    • Marked as answer by Raymond-Lee Friday, March 26, 2010 7:25 AM
    Friday, March 19, 2010 3:40 AM