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

1 comment:

  1. Las Vegas, NV Casinos & Entertainment Map | Mapyro
    Las Vegas 전라북도 출장마사지 Casinos & Entertainment Map. 강릉 출장마사지 Las Vegas Casinos 제주 출장마사지 & 동두천 출장샵 Entertainment Map Las Vegas, NV 창원 출장마사지 Casinos & Entertainment Map.

    ReplyDelete