I'm using OutlookSoft which uses MDX and MS OLAP. I'm trying to create
a calculated member that will provide inception-to-date.

Time dimension
1. time.history (balance)
2. fy06.total
a. 2006.Jul
b. 2006.Aug
c. ...etc.

User selects 2006.Oct:
So, ITD should be 1+2a+2b...all the way to 2006.Oct. However, my
formula only sums time.history + 2006.Oct (instead of summing
time.history >through< 2006.Oct.)

ITD Formula:
Sum([TIME.HISTORY]:ClosingPeriod(Month),([FULL.REVENUE]))

(full.revenue is a DataSrc dimension member)

Thanks for any help1

From your description, I'm not clear about the complete structure of the
Time dimension (for example, are all prior years aggregated in
time.history?). But you could try:

Sum({[TIME.HISTORY], PeriodsToDate(Year)},
([FULL.REVENUE]))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Time dimension levels look like this:
1. Time.History
2. All Years
a. FY05
b. FY06

So, the calc member would need to add time.history through the selected
time member.
Example, user selects 2006.OCT.
calc member should sum time.history and all periods up to 2006.oct.

Time.history is a balance account that doesn't roll up into anything
else. All other time periods roll up into a FY and "All Years".


Deepak Puri wrote:

In that case, you could define the calc. member like:

Sum(Ascendants([Time].CurrentMember),
iif([Time].FirstSibling is [Time].CurrentMember,
0, Sum([Time].FirstSibling:[Time].PrevMember,
([FULL.REVENUE]))))
+ ([FULL.REVENUE])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***