Home      Functions     Common Solutions      Contact     

MDX Functions:
MDX Functions

Common Solutions

Common Solutions
Resource Links

MDX Books
MDX Related Software
MDX Related Blogs
Other Links

Contact Us

Return all date members in the current year.

The YTD function will return Date members starting at the first day of the supplied year up to the supplied Date member.

The query below returns the members at the Date level for the Calendar hierarchy starting with the first Date member and ending with the supplied Date member. 

This query uses three VBA functions (NOW(), DATEADD(), and FORMAT()) to return a formatted date value that is five years in the past from today's date.

NOTE: Five years has been subtracted from today's date so the returned member would correspond to a date member that actually exists in the Adventure Works Date dimension. That way, the query could be executed against the Adventure Works cube and values would be returned.

WITH SET [YearToDateMems] AS
      YTD(STRTOMEMBER("[Date].[Calendar].[Date].["+ FORMAT(DATEADD("YYYY",-5,NOW()),"MMMM dd, yyyy") +"]"))
{[YearToDateMems]} ON 1
FROM [Adventure Works]


Copyright 2008 by MDXpert.com.
Terms of Use                              Privacy Statement
Site Powered By - WinHost.Com