Posted: 3/13/2012
Short of adding additional time dimension properties, is there any way through MDX to identify data associated with a specific (but somewhat relative) date? For example, I need to find the beginning market value (a specific measure) for the first business day of the calendar year, the first business day of the fiscal year, and the first business day of a quarter.
I'm assuming in your time dimension that you have specified if a day is a business day or not. I'm also assuming that you have identified the fiscal and calendar years in the dimension. If not, I don't think you're going to be able to identify the first business day of a year.
Here is an example I created with the AW db. There's probably a dozen ways to do this, but in this example I'm returning the first Tuesday of the year. You can use this calculation to get the measure for a day that you're looking for. I hope this helps.
with member [measures].[first day of year] as filter(descendants( ancestor([Date].[Calendar].currentmember, [Date].[Calendar].[Calendar Year]), [Date].[Calendar].[Date]),[Date].[Day Name] IS [Date].[Day Name].&[3]).Item(0).Name member [measures].[name] as [Date].[Calendar].currentmember.nameSelect {[measures].[first day of year],[measures].[name]} on 0,[Date].[Calendar].[Month] on 1From [Adventure Works]
Posted: 3/14/2012
Thanks, yes I do have the time dimension set with both fiscal and calendar years as well as an indicator of non business days. I will try why you have provided to see if I can get it to return the results I am expecting. Thanks for your help.
I found a better way to get what I was seeking. To follow your example, it would be:
[Date].[Calendar].currentmember.parent.parent.parent.firstchild.firstchild.firstchild
Posted: 3/15/2012
I don't know about better. It certainly is another way, though.
In the example I provided, my calculation will work from any level of the date hierarchy. Your example will only work from one specific level.