Showing posts with label ParallelPeriod. Show all posts
Showing posts with label ParallelPeriod. Show all posts

Wednesday, August 6, 2014

YearToDate (YTD) Previous Year (also QTD, MTD, WTD)

This is simple is you combine ParallelPeriod with the YTD functionality:

WITH
MEMBER [Measures].[Sales Amount YearToDate]
AS
    SUM(YTD( [Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount Previous Year]
AS
    SUM(ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount YearToDate Previous Year]
AS
    SUM(YTD( ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember)),[Measures].[Sales Amount])

Monday, August 4, 2014

Percentage difference from previous parallel period

Let’s start by getting the parallel period:

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"

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.

Thursday, July 31, 2014

Cousin

The Cousin functionality will get you the member on same position as the starting based on a new toplevel member. Most of the time you use it in the same way you use ParallelPeriod.

If you look at a date or calendar dimension is might look like this: All ==> Year ==> Semester ==> Quarter ==> Month ==> Date.

image

If you use a query like this:

Monday, July 28, 2014

MDX using member

The member function in MDX is comparable with the MS-SQL CTE functionality. It allows you to define a member before you use it in your query and thus keeping your query more readable.
An example:
WITH MEMBER [Measures].[Prior Year Sales] AS
    SUM(     
        ParallelPeriod
            (
                [Date].[Calendar].[Calendar Year],
                1,
                [Date].[Calendar].CurrentMember
            )
            ,
            [Measures].[Sales Amount]
       )
This calculates the SUM of the Sales Amount for the previous year using the ParallelPeriod functionality in combination with CurrentMember.