Tag Archives: ParallelPeriod

“Year to date” and “Last Year To Date” in MDX

Posted on by • Posted in MDX • Tagged , , , , • Leave a comment

To get “Year To Date” and “Last Year To Date” in MDX we can use the YTD function. To get “Last Year To Date” we also have to use the “ParallelPeriod” function. The “ParallelPeriod” gives us the same dates for an other period. In this case we switch back one year.

Here is a sample

WITH MEMBER [Year To Date] AS
( Sum (
Ytd ( [Date].[Calendar].CurrentMember ),
[Measures].[Internet Sales Amount]
) )
MEMBER [Last Year To Date] AS
( Sum (
Ytd (
ParallelPeriod ( [Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember )
),
[Measures].[Internet Sales Amount]
) )
MEMBER [Change] AS
divide ( [Measures].[Year To Date], [Last Year To Date] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Year To Date], [Measures].[Last Year To Date], [Measures].[Change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

And here is the result

YTD MDX

This code will work in Analysis Services Multidimensional. If you want to do the same for “Month to date”, “Quarter to date” or similar you can change YTD to MTD or QTD. If you plan to use week you should use an other hierarchy and then change to WTD.

I would recommend you to add this functionality as  calculations in the cube. There are several great scripts out there for you to copy. Such as DateTool by Marco Russo.

Comparable day in MDX using “lag” or “ParallelPeriod”

Posted on by • Posted in MDX • Tagged , , , , • Leave a comment

In many companies people want to compare todays sales with the comparable date last year. To find out what date the comparable day is we usually goes 364 days back from today.

In our sample today is 14 february 2012. And to see what the comparable date is we can use this query in SQL.

select '2012/02/14' as TodaysDate, DateName(dw, '2012/02/14') As 'DayName', 
dateadd(day, -364, '2012/02/14') As ComparableDay, DateName(dw, dateadd(day, -364, '2012/02/14')) As ComparableDayName

This will give this result

Comparable day 1

To check the sales for these days in MDX we can use these queries:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20110215] ON 1
FROM [Adventure Works]

The final query that compares today sales can look like this (using “lag”)

WITH MEMBER [Sales comparable day] AS
  ( [Date].[Calendar].CurrentMember .Lag ( 364 ),
  [Measures].[Internet Sales Amount] ),
  FORMAT_STRING = "Currency"
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Or this (using ParallelPeriod and Aggregate)

WITH MEMBER [Sales comparable day] AS
  Aggregate (
    ParallelPeriod ( [Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember ),
    [Measures].[Internet Sales Amount]
  )
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Comparable day 2

These samples can be run against the AdventureWorks cube in Multidimensional mode. To avoid error messages please make sure your time dimension has the members for the date you are comparing with. In this case 15 february 2011 has to exists in the time dimension. The -364 approach will have issues if last year was a leap years.