Solved

# MDX: Sales YTD Last year

Posted on 2006-06-02
1,536 Views
Hi,

I have been searching for a solution in MDX to create rolling Year-to-Date calculated members.

For example:
When the current member time is June 2006, I want the total sales YTD of last year, that
means from January 2005 till June 2005.

Thanks,

RS.
0
Question by:ikdachthetal

LVL 17

Expert Comment

Try this

where date between dateadd(year,-1,'6/1/2006') and '6/1/2006'
0

LVL 17

Expert Comment

sorry

0

Author Comment

Hi HuyBD,

thanks. But the year is not always fixed. It will be a MDX expression in a calculated member. The user could as well choose november 2004 as time period.

Regards,

RS.
0

Author Comment

For example:

Sales YTD = sum({ytd()},[Measures].[Sales])

But this formula is the sum YTD for this year. I'm searching for a formula for the sum YTD of last year.

0

LVL 17

Expert Comment

why dont you use sp

create stored procedure mysp(@date datetime)
as
begin
end
0

Author Comment

Basically it is an MDX expression in a calculated measure in an OLAP cube. Don't know if there is a link to stored procedures. I'll have to check that out. Don't have much experience with that either. I'll come back to it next Tuesday.
0

LVL 28

Accepted Solution

Hi,

Why won't you create a view on on time dimention table that will contain an additional column previous year and replace your table with this view in a cube that is how usually i do these things.
0

Author Comment

Thanks for all possible solutions.
0

## Featured Post

### Suggested Solutions

I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed