• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1549
  • Last Modified:

MDX: Sales YTD Last year

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
ikdachthetal
Asked:
ikdachthetal
  • 4
  • 3
1 Solution
 
HuyBDCommented:
Try this

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

where date <dateadd(year,-1,'6/1/2006') and datepart(year,date)=datepart(year,'6/1/2006')-1
0
 
ikdachthetalAuthor Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
ikdachthetalAuthor Commented:
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
 
HuyBDCommented:
why dont you use sp

create stored procedure mysp(@date datetime)
as
begin
select * from your table where where date <dateadd(year,-1,@date) and datepart(year,date)=datepart(year,@date)-1
end
0
 
ikdachthetalAuthor Commented:
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
 
imran_fastCommented:
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
 
ikdachthetalAuthor Commented:
Thanks for all possible solutions.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now