Asked by:
calculation with first day of the year.
Question

I'm having a measures defined on a yearly level (connected to the 1st january of each year).
I'm having a calculation which defines a result (ratio) for each day (lowest grain)
Now I want to multiply this ratio with that yearly measure but make it applicable for my entire period dimension.
When I "hardcode" the formula like "[Period].[date].&[20130101],[Measures].[ForeCast]" it works.
But ofcourse I want to make this "[Period].[date].&[20130101]" dynamic based on the selected member of the dimension period.
Is there a way to do this?
Thanks
All replies



the real solution would be to have the measure in another measure group, and bind it to the year level, and not the day level.
but here is the MDX you are looking for: (OpeningPeriod([Period].[date].[Year], [Period].[date].CurrentMember), [Measures].[ForeCast])

You might find that the above mdx returns the forecast for the year.
There are many ways to write the query. here's one, assuming day is 2 levels below year. It goes to the year of the current period, then the first month, then the first day of the month. If you have more levels between year and day, just add in more firstchilds.
(ancestor([Trading Dates].[Calendar YMD],[Trading Dates].[Calendar YMD].[Calendar Year]).firstchild.firstchild, [Measures].[ForeCast])
Richard
 Proposed as answer by RichardLees Monday, February 4, 2013 2:01 AM
 Marked as answer by Elvis LongModerator Monday, February 4, 2013 2:06 AM
 Unmarked as answer by BramDe Monday, February 4, 2013 8:36 AM
 Unproposed as answer by BramDe Monday, February 4, 2013 8:40 AM

The point is to make it work on every member of the dimPeriod, including in the hierarchy. Eventually I came it out on this:
(Measurs.Ratio * head(NonEmpty([Period].[Year].CurrentMember * [Period].[Date].Members * Measurs.[x]),1).Item(0))
It would be nice if you give me some query tuning options but this works for every selected member.
Thanks,
Bram
