# 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

• 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 Thursday, October 18, 2012 7:23 AM
• Marked as answer by 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])
```

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 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 Thursday, October 18, 2012 7:23 AM
• Marked as answer by 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