I am using MSAS2k5 and trying to find elegant MDX to build YTM (year to month
date function)
I would like to build the mdx statement that looks like:

with member [measures].[YTM]
as
'
sum(<<YTM date function>>,[measures].[number of meals])
'
select [measures].[YTM]
on 0,
(organization.customer.members
*organization.division.children
*organization.store.children)
on 1
from [Check Sales]
where organization.[customer name].[some company name]

thnx!
Alex

Hi Alex,

If you define YTM as Year to Month (up to the end of previous month),
then here's a sample Adventure Works query:

Sum(YTD(Ancestor([Date].[Calendar].CurrentMember,
[Date].[Calendar].[Month]).PrevMember),
[Measures].[Order Quantity])
select {[Measures].[Order Quantity],
[Measures].[YTMOrders]} on 0,
{[Date].[Calendar].[Month].&[2003]&[1],
[Date].[Calendar].[Date].&[574],
[Date].[Calendar].[Month].&[2003]&[2],
[Date].[Calendar].[Date].&[595],
[Date].[Calendar].[Month].&[2003]&[3],
[Date].[Calendar].[Date].&[618],
[Date].[Calendar].[Month].&[2003]&[4],
[Date].[Calendar].[Date].&[669]} on 1
from [Adventure Works]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

It is cool MDX - thnx!

I have modified slightly the MDX but it does not return anything but 'query
is executed successfully'???
I need to return YTM based on user input - for example 'October 2001'
I have calendar date hierarchy:
Cyear
Cquarter
Cmonth
Cweek
Cdate

appreciate your corrections:

With Member [Measures].[YTM Gross Sales] as
CoalesceEmpty(Sum(YTD(Ancestor([Date].[Calendar hrcy].[Cmonth].[October 2001],
[Date].[Calendar Hrchy].[CMonth]).PrevMember),
[Measures].[Chk Gross Sales Total]),0)

select {[Measures].[Chk Gross Sales Total]
,[Measures].[YTM Gross Sales]}
*{[Date].[Calendar hrcy].[Cmonth].[October 2001]}
on 0,

([Organization].[Organization hrchy].[customer name].members
,[Organization].[enterprise].children
,[Organization].[division].children
,[Organization].[store].children
,[Organization].[profit center].children)

on 1
from [items sale]
where [Organization].[customer].[IBM]

Thank you very much,
Alex Deiden


"Deepak Puri" wrote:

Depends on what result layout you want - but try this:

CoalesceEmpty(Sum(YTD(Ancestor([Date].[Calendar hrcy].CurrentMember,
[Date].[Calendar Hrchy].[CMonth]).PrevMember),
[Measures].[Chk Gross Sales Total]),0)

select {[Measures].[Chk Gross Sales Total]
,[Measures].[YTM Gross Sales]}
on 0,

([Organization].[Organization hrchy].[customer name].members
,[Organization].[enterprise].children
,[Organization].[division].children
,[Organization].[store].children
,[Organization].[profit center].children)

on 1
from [items sale]
where ([Organization].[customer].[IBM],
[Date].[Calendar hrcy].[Cmonth].[October 2001])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

That WORKS! Thank you , Deepak!
-Alex Deiden

"Deepak Puri" wrote: