# 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

Monday, January 28, 2013 9:29 AM

### All replies

• Nobody had to challange this before?
Wednesday, January 30, 2013 10:42 AM
• Do you mean you want a parameterized MDX? Any specific reason, not to use OPENINGPERIOD()

Cheers, Raunak | t: @raunakjhawar | My Blog

Wednesday, January 30, 2013 11:05 AM
• 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])

Wednesday, January 30, 2013 11:55 AM
• 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.

Richard

• Proposed as answer by Monday, February 4, 2013 2:01 AM
• Marked as answer by Monday, February 4, 2013 2:06 AM
• Unmarked as answer by Monday, February 4, 2013 8:36 AM
• Unproposed as answer by Monday, February 4, 2013 8:40 AM
Friday, February 1, 2013 3:54 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

Monday, February 4, 2013 8:39 AM
• For some Reason OpeningPeriod returns me the null value ...
Monday, February 4, 2013 8:50 AM