none
how to make YTD and MTD in the date calculation work for one date hierarchy

    Question

  • i built a date calculation dimension in the cube which has the following contents:

    ID Calculation
    1 Actuals
    2 Calendar Previous Period
    6 Last Ten Day Average
    4 Calendar Month To Date
    5 Last Five Day Average
    7 Calendar Year To Date

    and in the cube i have the following code for Year to date and month to date:

    SCOPE ([Date Calculations].[Calculation].[Calendar Year To Date]);                 
    THIS =
    AGGREGATE(

    periodstodate ([Date].[Hierarchy].[Cat Calendar Year],[Date].[Hierarchy].CurrentMember),
     
    [Date Calculations].[Calculation].[Actuals]);     
                   
     
    END SCOPE;
                      
    SCOPE ([Date Calculations].[Calculation].[Calendar Month To Date]);                 
    THIS =


    AGGREGATE(
     
    periodstodate ([Date].[Hierarchy].[Cat Calendar Year Month],[Date].[Hierarchy].CurrentMember),
     
    [Date Calculations].[Calculation].[Actuals]);     
                   
     
    END SCOPE;

    in my date dimension, i have hierarchy as following:

    Year-->Month-->Date

    if i just drag the hierarchy from Month-->Date, all the calculation works good, but if i try to use the whole hierarchy, somehow the YTD and MTD can not work together....it will show error when i try to add the date calculation dimension for the pivot table, any idea how i can implement both and work for one whole hierarchy?

    Thanks

    Jimmy


    • Edited by jimmyji168 Sunday, September 15, 2013 12:15 AM
    Sunday, September 15, 2013 12:13 AM

Answers

All replies

  • I think you need to scope your calculations to the appropriate level. For instance, in the query above, the Row axis is at the Year level. Yet, you've got calculations for Calendar Month to Date and Last 5 Day Average. You may want to scope the Calendar Month to Date calculation so that it only applys to days.

    Just guessing though. Martin


    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    Sunday, September 15, 2013 1:23 AM
  • Hi,

    Perhaps this blog post can shed some light on your problem.

    http://mdxdax.blogspot.se/2011_10_01_archive.html

    BR

    Thomas Ivarsson

    Sunday, September 15, 2013 10:03 AM
    Moderator