MDX MTD Calculation for Given Day In Month

I'm trying to get a MTD calculation up to a given day in a month for current and previous months. This is for a Reporting Services report. The columns will be Total Calls and Total Calls Through {X} Days in Month. The rows will just have the months listed. I was able to do this in RS 2000 using the following dynamic mdx statement:

= "WITH MEMBER [Measures].[ParallelPeriod] AS " +

" 'Sum({[TimesDaily.Calendar].CurrentMember.[1]:[TimesDaily.Calendar].CurrentMember.[" & Now.Day.ToString()& "]}, " + " [Measures].[Calls])' " +

" SET Months AS " +

" 'LastPeriods(" + CStr(Parameters!monthsback.value)+ " , Tail({[TimesDaily.Calendar].Monthname.Members}, 1).item(0))' " +

" select {[Measures].[Calls], [Measures].[ParallelPeriod]} on columns, " +

" NON EMPTY {Months} on rows " +

" from Calls " +

" Where(" + Parameters!px_workflows.value+ ") "

I'm trying to reproduce this query in SSAS and RS 2005 and I can't seem to do it. My time dimension "[TimesDaily]" has one hierarchy, with the levels of [Yearname], [Monthname], and [Dayval]. I've tried variations of the following calculated member:

Sum(Descendants([TimesDaily].[Hierarchy].CurrentMember, [TimesDaily].[Hierarchy].[Monthname]), [Measures].[Calls])

This doesn't seem to be working, even without the filter for day. I need to be able to sum the values for a month, and be able to only take those days in the month that are prior to the current day. I.E., today's report would sum up the values for days 1-23 in each month listed in the report. Is there any way to do this using just CurrentMember? Any help would be greatly appreciated.

I actually figured out a way to do it. I first created a calculated member to get the current day:

[Measures].[CurrentDay] =

Tail({[TimesDaily].[Hierarchy].[Dayval].Members}, 1).item(0).MemberValue

Then I used topcount with the currentday member

[Measures].[Calls_MTD] =

Sum(TopCount(Descendants([TimesDaily].[Hierarchy].CurrentMember, 1), [Measures].[CurrentDay]), [Measures].[Calls])

I'm sure there's other ways to do this as well, like creating a Measure from the Time Dimension table and use the LastChild aggregation.

|||

If I read your requirmenst right, it's actually much simpler than this:

First, I'm going to assume you have the TimeDaily set up correctly and the corretc meta data for it. If your not sure how this should be done, you can tie yourself up in knots trying to figure it out.

Second, your members could easily be:

With

Measures.myMTD as Sum( MTD( [TimesDaily].[Hierarchy].[Dayval].CurrentMember ), [Measures].[Calls] )

Measures.myMTDprev as Sum( MTD( ParrallelPeriod( [TimesDaily].[Hierarchy].[Monthval], 1, [TimesDaily].[Hierarchy].[Dayval].CurrentMember )), [Measures].[Calls] )