SSAS FAQ MDX FAQ MDX-How can I get Last (Previous) Year to Date (YTD) values?
MDX-How can I get Last (Previous) Year to Date (YTD) values?
User Rating: / 1
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.

 

 
Comments (8)
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])
Strategy Companion