Microsoft Developer Network

Calculating Year-to-Date (YTD), Month-to-Date (MTD), and Quarter-to-Date (QTD) in SQL Server Analysis Services

A common requirement in business intelligence reporting is to analyze data on a Year-to-Date (YTD), Month-to-Date (MTD), or Quarter-to-Date (QTD) basis. SQL Server Analysis Services (SSAS) provides powerful tools to implement these calculations efficiently, typically using MDX (Multidimensional Expressions). This article will guide you through the common methods for creating these time-intelligence calculations within your SSAS multidimensional models.

Understanding the Concepts

Before diving into the MDX, let's clarify what each term means:

• Month-to-Date (MTD): The sum of a measure from the first day of the current month up to the most recent date in the current month for which data exists.
• Quarter-to-Date (QTD): The sum of a measure from the first day of the current quarter up to the most recent date in the current quarter for which data exists.
• Year-to-Date (YTD): The sum of a measure from the first day of the current year up to the most recent date in the current year for which data exists.

Prerequisites

To implement these calculations, your SSAS model must have a well-defined Date dimension. This dimension should include attributes such as:

• Date Key (unique identifier for each day)
• Full Date
• Month Name
• Month Number
• Quarter Name
• Quarter Number
• Year

Crucially, the Date dimension should be marked as a Date dimension in your cube.

MDX Calculations for Time Intelligence

1. Month-to-Date (MTD)

The MTD calculation sums values from the beginning of the current month up to the current period. We can use the ParallelPeriod function combined with PeriodsToDate or a direct SUM with slicing.

WITH MEMBER [Measures].[Sales MTD] AS
    SUM(
        PeriodsToDate([Date].[Calendar].CalendarMonth, [Date].[Calendar].CurrentMember),
        [Measures].[Sales Amount]
    )
SELECT
    {[Measures].[Sales Amount], [Measures].[Sales MTD]} ON COLUMNS,
    [Date].[Calendar].[Date].MEMBERS ON ROWS
FROM [YourCubeName]
WHERE ([Date].[Calendar].CurrentDateMember)
            

WITH MEMBER [Measures].[Sales MTD - ParallelPeriod] AS
    SUM(
        Tail(
            PeriodsToDate([Date].[Calendar].CalendarMonth, [Date].[Calendar].CurrentMember),
            1
        ),
        [Measures].[Sales Amount]
    )
SELECT
    {[Measures].[Sales Amount], [Measures].[Sales MTD - ParallelPeriod]} ON COLUMNS,
    [Date].[Calendar].[Date].MEMBERS ON ROWS
FROM [YourCubeName]
            

2. Quarter-to-Date (QTD)

Similar to MTD, QTD calculates the sum from the beginning of the current quarter.

WITH MEMBER [Measures].[Sales QTD] AS
    SUM(
        PeriodsToDate([Date].[Calendar].CalendarQuarter, [Date].[Calendar].CurrentMember),
        [Measures].[Sales Amount]
    )
SELECT
    {[Measures].[Sales Amount], [Measures].[Sales QTD]} ON COLUMNS,
    [Date].[Calendar].[Date].MEMBERS ON ROWS
FROM [YourCubeName]
            

3. Year-to-Date (YTD)

YTD calculations sum values from the beginning of the current year.

WITH MEMBER [Measures].[Sales YTD] AS
    SUM(
        PeriodsToDate([Date].[Calendar].CalendarYear, [Date].[Calendar].CurrentMember),
        [Measures].[Sales Amount]
    )
SELECT
    {[Measures].[Sales Amount], [Measures].[Sales YTD]} ON COLUMNS,
    [Date].[Calendar].[Date].MEMBERS ON ROWS
FROM [YourCubeName]
            

Using the Date Hierarchy

The key to these calculations is correctly referencing the date hierarchy. For example, [Date].[Calendar].CalendarMonth refers to the month level within your 'Calendar' hierarchy in the 'Date' dimension. Ensure your dimension and hierarchy names match your SSAS model.

Best Practices and Considerations

• Performance: For large datasets, ensure your date dimension is properly indexed and that the measure being aggregated (e.g., [Measures].[Sales Amount]) is a standard aggregate measure.
• Current Date Handling: The examples above often assume a context for the current date. In dynamic reporting tools (like Power BI or Excel PivotTables), the "current" context is usually determined by the user's selection. For static reports or specific scenarios, you might need to explicitly define the end date using functions like NOW() or TODAY(), although this is less common in standard SSAS MDX measures.
• Fiscal Calendars: If your organization uses a fiscal calendar, you'll need to create a fiscal date dimension and adapt the MDX to use fiscal periods (e.g., FiscalYear, FiscalQuarter, FiscalMonth).
• Shared Measures: It's good practice to define these time-intelligence calculations as Shared Measures in your SSAS solution, so they can be reused across multiple cubes.

By leveraging MDX and a well-structured Date dimension, you can provide powerful time-intelligence capabilities to your users, enabling deeper insights into business performance.