Showing posts with label Quarter To Date (QTD). Show all posts
Showing posts with label Quarter To Date (QTD). 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])

PeriodsToDate, YearToDate (YTD), QuarterToDate (QTD), MonthToDate (MTD), WeekToDate (WTD)

PeriodsToDate, YearToDate (YTD), QuarterToDate (QTD), MonthToDate (MTD), WeekToDate (WTD) are all MDX functions which aggregate date from de first date of a chosen period up and an including a given end date.