Finding Mesures for Specific Dates using MDX

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  analysis services   » Finding Mesures for Specific Dates using MDX

Finding Mesures for Specific Dates using MDX

Topic RSS Feed

Posts under the topic: Finding Mesures for Specific Dates using MDX

Posted: 3/13/2012

Jedi Youngling 10  points  Jedi Youngling
  • Joined on: 6/10/2010
  • Posts: 5

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.


Posted: 3/13/2012

Jedi Master 5620  points  Jedi Master
  • Joined on: 11/2/2009
  • Posts: 237

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.name

Select {[measures].[first day of year],[measures].[name]} on 0,

[Date].[Calendar].[Month] on 1

From [Adventure Works]

Posted: 3/14/2012

Jedi Youngling 10  points  Jedi Youngling
  • Joined on: 6/10/2010
  • Posts: 5

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.


Posted: 3/14/2012

Jedi Youngling 10  points  Jedi Youngling
  • Joined on: 6/10/2010
  • Posts: 5

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

Jedi Master 5620  points  Jedi Master
  • Joined on: 11/2/2009
  • Posts: 237

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.


Page 1 of 1 (5 items)