Monday, June 9, 2014

SSAS Dynamic Prior Period


When using the prior period function and you want the calculation to work on any level of the hierarchy, you'll need to remember the "CurrentMember.Level" syntax:

CREATE MEMBER CURRENTCUBE.[Measures].[View Count - Prior Period]
AS (
       ParallelPeriod (
              [Dim Date].[Calendar Year Hierarchy].CurrentMember.Level
              , 1
              , [Dim Date].[Calendar Year Hierarchy].CurrentMember
       , [Measures].[View Count]


This will allow the calculation to show Prior Year when looking at the year level, Prior Month when looking at the month level, etc.

No comments:

Post a Comment

Please only include comments that add to the substance of the Blog Post:
- Question or request for clarification
- Idea for improvement
- Identifying an issue

Please refrain from promotion.