Breaking down goals

Hi,

we have a fact-table, containing sales-goales. Each goal relates to a year and a sales-person. We want to break down this annual goals as follows:

If the sales is $ 1000 for 2007 then the goal for 2007-12-31 is $ 1000; for 2007-12-30 the goal would be $ 1000 / 365 * 364; for 2007-12-29 $ 1000 / 365 * 363 and so on.

How to accomplish this with SSAS ?

Thx and Regards,

Manfred

you could do something like

Measures.[Sales Goal] * (count(YTD([Time].[Day].CurrentMember)) / 365)

You would probably want to scope such a calculation so that it only occurred at the day level as it would not work at higher levels.

Another approach might be to add a "Day of Year" attribute or even a "YTD proportion" to your time dimension which does this calculation in the DSV, then you don't have to dynamically count the days all the time and you could do something like.

Measures.[Sales Goal] * [Time].[YTD Proportion].MemberValue