Level-Based PeriodToDate Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
These functions return the set of members from the start of a containing period up to the current member.
They are shortcuts for the 'PeriodsToDate' function at common time boundaries.
YTD — Year to Date
Description: Returns the set of sibling members from the start of the year up to and including the current member.
Syntax: YTD([Member_Expression])
Input: Optional member (defaults to current context member)
Output: Set of period members from January 1 through current date
Business Use Case: Calculate cumulative sales from January through the current month.
WITH MEMBER [Measures].[YTD Sales] AS SUM(YTD([Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[YTD Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Expected Output: January shows same value as monthly; each subsequent month accumulates.
Variation — YTD with prior year comparison:
WITH MEMBER [Measures].[YTD Sales] AS SUM(YTD([Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount]) MEMBER [Measures].[Prior YTD] AS
SUM(YTD(ParallelPeriod(
[Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[YTD Sales], [Measures].[Prior YTD]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]MTD — Month to Date
Description: Returns the set of sibling members from the start of the month up to and including the current member (useful at day-level granularity).
Syntax: MTD([Member_Expression])
Input: Optional member expression
Output: Set of day members within the current month
Business Use Case: Track daily cumulative revenue within the current month.
WITH MEMBER [Measures].[MTD Sales] AS SUM(MTD([Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[MTD Sales]} ON COLUMNS,
[Date].[Calendar].[Date].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].[January 2012]QTD — Quarter to Date
Description: Returns the set of sibling members from the start of the quarter up to and including the current member.
Syntax: QTD([Member_Expression])
Input: Optional member expression
Output: Set of month members within the current quarter
Business Use Case: Show quarterly running total alongside monthly actuals.
WITH MEMBER [Measures].[QTD Sales] AS SUM(QTD([Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[QTD Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]WTD — Week to Date
Description: Returns the set of sibling members from the start of the week up to and including the current member.
Syntax: WTD([Member_Expression])
Input: Optional member expression
Output: Set of day members within the current week
Business Use Case: Track week-to-date sales for daily operations monitoring.
WITH MEMBER [Measures].[WTD Sales] AS SUM(WTD([Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[WTD Sales]} ON COLUMNS,
[Date].[Calendar].[Date].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].[January 2012]