How to compute a half year to full year comparison?

Hello everyone,

Here is the formula I am trying to:

(the actual cost from Jan to Jun) / (the budget from Jan to Dec)

If I use → Head ( Descedants ( [Date].CurrentMember, [Date].[Month] ) , 6 ) , I can get the first 6 months of that year. However, if the time data is not complete, e.g., there's only July to Dec on 2004, the months I get will be July to Dec and that won't be the output I expect.

Is there a better way to the question? Any idea would be appreciated.

Thanks,
Hank

Hi Hank,

you could create a new attribute for the month number (vales 1-12) with the sql-function Month()

then select (in mdx) the range from monthnumber 1 to monthnumber 6 an join it with the current year and sum that set

so your half-year value would be calculated as follows:

sum({[Time].[MonthNumber].[1]:[Time].[MonthNumber].[12]}*[Time].[Year].[2004])

should work but no warranty

greets

gerhard

|||

Thanks a lot, Gerhard.

I didn't think about using a join with the current year. This solves my problem.

Regards,

Hank