MDX-How can I get Last (Previous) Year to Date (YTD) values?
User Rating: / 30
PoorBest 
Written by Vidas Matelis   
Tuesday, 01 May 2007 19:37

Q: How can I get Last (Previous) Year to Date (YTD) values?

A: First lets clarify question. Lets say you selected date March 22, 2004. Year To Date means you are interested in the interval from January 1, 2004 up to March 22, 2004. Last (Previous) Year to date means you are interested in the intervale January 1, 2003 up to March 22, 2003.

With certain exceptions, you should be able to use function ParallelPeriod to get a date in the previous year matching your selected date. Assumption here is that you have similar members in each years hierarchy. If, for example, you have just business days in day hierarchy, matching might be different.

Example: Lets say you selected March 22, 2004. If we use ParallelPeriod function, we can get back member for date March 22, 2003. Query Example:

SELECT
{[Measures].[Internet Order Quantity]} ON 0
, {[Date].[Calendar].[Date].[March 22, 2004]
, ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, [Date].[Calendar].[Date].[March 22, 2004]
)
} ON 1
FROM [Adventure Works]

Result:

Internet Order Quantity
March 22, 2004 147
March 22, 2003 7

As you know how to get previous years matching day, you can calculate YTD for previous year:

WITH MEMBER [Measures].[Current YTD] AS
SUM(YTD([Date].[Calendar].CurrentMember), [Measures].[Internet Order Quantity])
MEMBER [Measures].[Last YTD] AS
SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, [Date].[Calendar].CurrentMember))
, [Measures].[Internet Order Quantity]
)
SELECT {[Measures].[Current YTD]
, [Measures].[Last YTD]
} ON 0
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[March 22, 2004])

Results are:

Current YTD Last YTD
12,557 718

Now lets run the same query, but substitute YTD and Parallel periods with hardcoded dates. This way we will make sure that calculation is right.

WITH MEMBER [Measures].[Current YTD] AS
SUM({[Date].[Calendar].[Date].[January 1, 2004]:[Date].[Calendar].[Date].[March 22, 2004]}
, [Measures].[Internet Order Quantity])
MEMBER [Measures].[Last YTD] AS
SUM( {[Date].[Calendar].[Date].[January 1, 2003]:[Date].[Calendar].[Date].[March 22, 2003]} -- Jan 1, 2003: March 22, 2003
, [Measures].[Internet Order Quantity]
)
SELECT {[Measures].[Current YTD]
, [Measures].[Last YTD]
} ON 0
FROM [Adventure Works]

Results match previous query:

Current YTD Last YTD
12,557 718

Note: You can substitute YTD function with PeriodsToDate function.
These tests were done on Adventure Works database.

 

< Prev   Next >
 
Comments (15)
15 Friday, 07 October 2011 06:39
bhanuja
Thank u,This Query was Helped to Me.
14 Saturday, 23 April 2011 11:27
laxman
i got this error in mdx
Errors related to feature availability and configuration: The 'Measure expressions' feature is not included in the 'Standard Edition' SKU. 0 0
13 Wednesday, 22 September 2010 02:06
Harsu
Really interesting but how do I compare same day last year ? Let me explain.

My Client has a requirement where they compare same day last year i.e. Monday with Monday, Tues with Tues !!! I know that the day will not be the same!
eg. the want to compare Thursday, 1 Jul 2010 = Thursday, 2 Jul 2010

so ideally it is Date - 364 !!

Any ideas?
12 Tuesday, 01 June 2010 13:38
Biggles
Im having a problem getting my head around some MDX.

I have managed to create my YTD value using:
SUM(YTD([Calendar].[Year - Period - Date].CurrentMember), [Measures].[Sales Value])

I have my last year YTD value with:
SUM(YTD(ParallelPeriod([Calendar].[Year - Period - Date].[Year]
, 1
, [Calendar].[Year - Period - Date].CurrentMember))
, [Measures].[Sales Value]
)

However I need to create a Full Last Year Value. (which is the same as the Last Year YTD value if it were run on the 31st Dec), basically the full value of the year.
I have SUM([Calendar].[Year].currentmember.prevmember, [Measures].[Sales Value]) but this only displays the correct value against the year or date level but not on the period (Probably as a period looks like [02 2010] etc.
my date heirachy is [Year] - [Period] - [Date]
Does anyone have any idea of what I can do to get it on the Period Level?

thanks. Andrew
11 Friday, 30 April 2010 06:31
Mohamed Irshad
Cool article. It instantly helped me resolve calculating previous year YTD measures.
10 Thursday, 04 March 2010 13:55
Blaze
It is perfect working for but What I need is instead:
"FROM [Adventure Works] WHERE ([Date].[Calendar].[Date].[March 22, 2004])"

FROM [Adventure Works] WHERE (Last Load in time & Not Empty)"
9 Tuesday, 05 January 2010 16:23
Nagendra Prasad
The Last YTD is fine as per the code :
===========================================
WITH MEMBER [Measures].[Current YTD] AS
SUM({[Date].[Calendar].[Date].[January 1, 2004]:[Date].[Calendar].[Date].[March 22, 2004]}
, [Measures].[Internet Order Quantity])
MEMBER [Measures].[Last YTD] AS
SUM( {[Date].[Calendar].[Date].[January 1, 2003]:[Date].[Calendar].[Date].[March 22, 2003]} -- Jan 1, 2003: March 22, 2003
, [Measures].[Internet Order Quantity]
)
SELECT {[Measures].[Current YTD]
, [Measures].[Last YTD]
} ON 0
FROM [Adventure Works]
=================================

NOTE : This mean Last YTD is here as true, now my question is if we wish to retrieve data for Last YTD as false then how we can do it.

I mean, looking data all except Last YTD.

Please help me do it and provide the mdx.

Thanks in advance.
8 Tuesday, 15 July 2008 16:03
khotso koetle
Thank You- This was really helpful.

Regards
7 Wednesday, 26 March 2008 22:22
admin
Link to MSDN forum where you can post questions:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1
6 Monday, 24 March 2008 23:29
admin
Jhon, please post questions that are not related to this FAQ at MSDN forum. It is best if you formulate your question based on Adventure Works DW database structure, and you'll most likely will get timely answers.
5 Monday, 24 March 2008 21:09
Jhon
I want to do grouping on the base of Location and Hierarchy, Depends what user selects,If user select Location, The data should be group by Location, on the other hand if user select multiple Hierarchy such as region, area etc, The data should be grouped by those parameters. Any idea how that would be done?
4 Monday, 24 March 2008 18:28
admin
Jhon, Please read about dimension types here:
http://technet.microsoft.com/en-us/library/ms175589.aspx
Your Time dimension probably is not specified as time, and attribute for year is not specified as Year. In such case you cannot use YTD. You need to fix structure. Also, you can rewrite this using PeriodsToDate function. Just instead of dealing with attributes start working with hierarchies, that is [Date].[YourHierarchyName].[Month], etc.
3 Monday, 24 March 2008 18:18
Jhon
I executed the above query, It gives me error: "A year level was expected, No such level was found in cube."
I have year level like this, [Date].[Year].[Year].[All].
I tried to replace hierachy level still did not work.
2 Monday, 24 March 2008 16:46
admin
Try to debug this by splitting query:
Execute:
SELECT {YTD([Date].[Year].CurrentMember)} ON 0
, {[Measures].[NRT Count]} ON 1
FROM [Data Warehouse]

Do you see expected results. If no, replace YTD with PeriodsToDate and specify proper level. If PeriodsToDate works, your date dimension does not have proper "Type" property set.
1 Monday, 24 March 2008 16:37
Jhon
I tried this query to see get YTD for the current year and previous year but it gives me null values for both.
WITH MEMBER [Measures].[Current YTD] AS
SUM(YTD([Date].[Year].CurrentMember), [Measures].[NRT Count])
MEMBER [Measures].[Last YTD] AS
SUM(YTD(ParallelPeriod([Date].[Year].[Year].[2007]
, 1
, [Date].[Year].CurrentMember))
, [Measures].[NRT Count]
)
SELECT {[Measures].[Current YTD]
, [Measures].[Last YTD]
} ON 0
FROM [Data Warehouse]
WHERE ([Date].[Year].[Year].[2007])

Tags: faq, mdx
 

Report Portal