Subscribe to this blog
Briefcase
Print
Developing MDX calculations is one of the most confusing and time consuming pieces of building an Analysis Services cube. That is why I have accumulated what I like to call a Utility Belt of calculation that I can pull out at any moment to solve a problem. I consider this a Utility Belt because on just about every Analysis Services project that I have worked on these calculations have come in handy and I hope they will be helpful for you.
When building calculations you may find the templates that are available in Business Intelligence Development Studio (BIDS) as a helpful starting point. Unfortunately, I have found even using these templates can make calculations more confusing than they really need to be.
In this blog series I will give you the business problem, solution, and then calculation that satisfies the business problem.
Problem
You need to return sales for one year prior (Ex. one year ago, four quarters ago, twelve months ago).
Solution
Use the ParrallelPeriod time series function to look back a set number of values back in a date hierarchy and return the sales at that point.
Calculations
Looking back one year at the Year level
(ParallelPeriod([Date].[Date Hierarchy].[Year],
1,
[Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])
---------------------------------------------------------------
Looking back four quarters at the Quarter level
(ParallelPeriod([Invoice Date].[Date Hierarchy].[Quarter],
4,
[Invoice Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])
---------------------------------------------------------------
Looking back twelve months at the Month level
(ParallelPeriod([Invoice Date].[Date Hierarchy].[Month],
12,
[Invoice Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])
You can find me regularly blogging at http://www.bidn.com/blogs/DevinKnight
Posted by Wes W. on 4 December 2009
Hi Devin,
Have you ever been successful getting a SSAS dynamic named set calculation to work in Excel based on ParallelPeriod?
For example, I have the following dynamic named set calculation to show the previous quarter, where 'Today' is a top-level named set and works fine.
ParallelPeriod
(
[Date].[Fiscal].[FiscalQtr]
,1
,Exists
(
[Date].[Fiscal].[FiscalQtr].MEMBERS
,[Today]
).Item(0).Item(0)
)
The above code oddly works fine everywhere except in Excel where no result set is shown. This one has me stumped and I'm not sure if it's an Excel issue or not yet.
Posted by knight_devin@hotmail.com on 7 December 2009
That's strange. I haven't had the problem where my calculations will not work in Excel. I'll try and replicate the problem.
Posted by raj more on 7 December 2009
Should this be a "-1" for the number of years as in:
(ParallelPeriod([Date].[Date Hierarchy].[Year],
-1,
[Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])
We are going a year back in time.
Posted by knight_devin@hotmail.com on 7 December 2009
Actually positive 1 is correct in this calculation.
Posted by Zenatti on 10 January 2010
Any news aboutcalculations not working in Excel?