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])

SELECT
{
     [Measures].[Sales Amount]
    ,[Measures].[Sales Amount YearToDate]
    ,[Measures].[Sales Amount Previous Year]
    ,[Measures].[Sales Amount YearToDate Previous Year]
} ON COLUMNS,
{
    [Date].[Calendar].[Date].&[20120101]:[Date].[Calendar].[Date].&[20120216]
}
ON ROWS
FROM [Adventure Works]

This will get us:

image

It works the same for Quarter To Date (QTD), Month To Date (MTD) and Week to Date (WTD)

Have Fun And Till Next Time

No comments:

Post a Comment