none
Current Reporting Month and YTD calculations

    Question

  • Hi.

    I'm trying to design a report in Excel based on a cube that will default to the current reporting month (which is actually last month), but still allow the user to select a different month to report on.  My date dimension table has a column called CurrentRepMonth, which is set to 'Current Reporting Month' for all days in the previous month.

    My date dimension has a hierarchy of Year - Month - Date, and an attribute called 'Current Reporting Month'.

    I have a calculation in my cube to get the YTD hours worked, based on the selected month:

    CREATE MEMBER CURRENTCUBE.[Measures].[YTD - Hours Worked]
     AS AGGREGATE ( YTD ([Date].[Year -  Month -  Date].CurrentMember), [Measures].[Hours Worked]), 

    In excel, if you select a month in the filter, the YTD calculation worked.  However, if you filter by Current Reporting Month, the calculation returns blank.

    Any help is much appreciated.

    Thursday, October 18, 2012 3:08 AM

Answers

  • Okay, try this code,

    SCOPE(([Date].[Year -  Month -  Date].Members);

    [Measures].[YTD - Hours Worked]= AGGREGATE ( YTD ([Date].[Year -  Month -  Date].CurrentMember), [Measures].[Hours Worked])

    END SCOPE;

    SCOPE(([Date].[Current Reporting Month].Members);

    [Measures].[YTD - Hours Worked]=// alternate logic to get YTD, you cannot take advantage of hierarchy here

     END SCOPE;


    • Edited by Yogisha Bhat Thursday, October 18, 2012 7:23 AM
    • Marked as answer by cmn002 Friday, October 19, 2012 6:22 AM
    Thursday, October 18, 2012 7:21 AM

All replies

  • In your calculation you are refering to [Date].[Year -  Month -  Date] hierarchy.   'Current Reporting Month' is an attribute which is not part of the hierarchy.

    If you want to achive what you want you can write a scope statement that will refer to [Date].[Current Reporting Month] in your calculation when you select 'Current Reporting Month' instaed of herarchy member.

    Thursday, October 18, 2012 4:42 AM

  • You can try using the following MDX:

    sum(PeriodsToDate([Period].[Fin Period].[Year],[Period].[Fin Period].currentmember),[Measures].[Revenue])
    


    Please vote as helpful or mark as answer, if it helps Regards, Anand

    Thursday, October 18, 2012 4:53 AM
  • Hi Yogish.

    Thanks for the quick reply.  I'm pretty new to MDX.  Could you give me an example?


    • Edited by cmn002 Thursday, October 18, 2012 5:28 AM
    Thursday, October 18, 2012 5:17 AM
  • Okay, try this code,

    SCOPE(([Date].[Year -  Month -  Date].Members);

    [Measures].[YTD - Hours Worked]= AGGREGATE ( YTD ([Date].[Year -  Month -  Date].CurrentMember), [Measures].[Hours Worked])

    END SCOPE;

    SCOPE(([Date].[Current Reporting Month].Members);

    [Measures].[YTD - Hours Worked]=// alternate logic to get YTD, you cannot take advantage of hierarchy here

     END SCOPE;


    • Edited by Yogisha Bhat Thursday, October 18, 2012 7:23 AM
    • Marked as answer by cmn002 Friday, October 19, 2012 6:22 AM
    Thursday, October 18, 2012 7:21 AM
  • Thanks Yogish.  That worked.
    Friday, October 19, 2012 6:22 AM