Get year information from month levels

Question

Hi,

I have a date dimension hierarchy in my cube as below.

Year --> Quarter --Month

How can i get the year information at the month level? Is there any function to extract year information?? like ::

YEAR( [Service Date].[Service Date].[Service Months].&[4]&[Apr 2011] ) would result in 2011..... is this possible?

Thanks in advance,

Anish

---

Anish Tuladhar -Do not forget to mark as answer, only if helpful.:)

Answer 1

Hi Anish,

You should able to get year by using "Ancestor" function.

I am assuming name of hierarchy is [Service Date]

Year --> Month--> Date

WITH
MEMBER [Measures].[The Year] AS
Ancestor
    (
    [Service Date].[Service Date].CurrentMember,
    [Service Date].[Service Date].[Year]
    ).Name

should return you the name of the year .

A similar example from Adventure works will be look like as below

   

Answer 2

Thanks asim .... this was a bang! :)

---

Anish Tuladhar -Do not forget to mark as answer, only if helpful.:)