Sunday, August 3, 2014

ParallelPeriod

The ParallelPeriod functionality is closely related to the Cousin functionality. The main difference is that the ParallelPeriod function expects a hierarchy of the type Time.
It will get you the value of a measure based given top-level.
WITH MEMBER [Measures].[Sales Amount PY]
AS
(
    [Measures].[Sales Amount]
    , ParallelPeriod         ([Date].[Calendar].[Calendar Year] // Level
        , 1 // number of periods back
        , [Date].[Calendar].CurrentMember) // Start member
    )
   , format_string = "Currency"
SELECT
    {
          [Date].[Calendar].[Date].&[20120228]
        , [Date].[Calendar].[Date].&[20110228]
        , [Date].[Calendar].[Date].&[20120229]
        , [Date].[Calendar].[Date].&[20120301]
        , [Date].[Calendar].[Date].&[20110301]
    } ON COLUMNS
    ,
    {
          [Measures].[Sales Amount]
        , [Measures].[Sales Amount PY]
    } on ROWS
FROM [Adventure Works]
Give you:
image
Have Fun And Till Next Time

No comments:

Post a Comment