There are total 9 Time Function available in MDX & MSBI developer people like to use them in their reports.
1-Function Name: ClosingPeriod
Syntax: CLOSINGPERIOD( [«Level»[, «Member»] ] )
Description: The ClosingPeriod function returns the last sibling from the descendants of a supplied member at a supplied level.
The query above returns the default measure for the closing period at the Calendar Month level for the member CY 2008.
2-Function Name: LastPeriods
Syntax: LASTPERIODS( «Index»[, «Member»] )
Description: The LastPeriods function returns the supplied number of members up to and including the supplied member.
The above query returns the first 15 days of July 2005 in the Calendar hierarchy in the Date dimension.
3-Function Name: Mtd
Syntax: MTD( [«Member»] )
Description: The MTD function returns all siblings of the supplied member starting at the first sibling and ending with the supplied sibling.
The above query returns all siblings up to and including the member July 8, 2005 from the Calendar hierarchy in the Date dimension.
4-Function Name: OpeningPeriod
Syntax: OPENINGPERIOD( [«Level»[, «Member»] ] )
Description: The OpeningPeriod function returns the first sibling from the descendants of a supplied member at a supplied level.
The query above returns the default measure for the opening period at the Calendar Month level for the member CY 2008.
5-Function Name: ParallelPeriod
Syntax: PARALLELPERIOD( [«Level»[, «Numeric Expression»[, «Member»] ] ] )
Description: The ParallelPeriod function returns a member from a prior period in the same relative position as a supplied member.
The query above returns the member Q2 CY 2008 which is at the supplied Calendar Quarter level (Q1 CY 2004) and three prior periods (Calendar Year) away from the supplied member of Q1 CY 2004.
6-Function Name: PeriodsToDate
Syntax: PERIODSTODATE( [«Level»[, «Member»] ] )
Description: The PeriodsToDate function returns a set of siblings from the same level as the supplied member. The returned set starts with the first sibling and ends with the supplied member.
The query above returns the first sibling at the Date level up to the supplied member of March 7, 2008.
7-Function Name: Qtd
Syntax: QTD( [«Member»] )
Description: The QTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.
The query below returns a set of dates starting with the first day of the quarter (July 1, 2005) and ending with supplied date of July 07, 2005.
8-Function Name: Wtd
Syntax: WTD( [«Member»] )
Description: The WTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.
If you run the above statement in SQL Server Management Studio, you would receive the following error: Executing the query ... Query (1, 8) By default, a week level was expected. No such level was found in the cube. Execution complete.
9-Function Name: Ytd
Syntax: YTD( [«Member»] )
Description: The YTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.
The query above returns a set of dates starting with July, 2005 and ending with July 14, 2005.
Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2
