SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

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