Balance Forwarding

Hi,

Just want to seek advice on how to forward the ending balance of Balance Sheet Accounts of previous year to the current year.

Marvs

You can use a couple of MDX function depending on the level you are standed.

If we have the following time heriarchie:

[DimTime].[Year-Month-Day-H] = [Year], [Month], [Day]

And assuming that the [Balance] measure is additive

1) if you drill down to the day level you can obtain the ending balance of the previous year thorugh the following MDX sentence:

(ParallelPeriod([Year],1,[Dim Time].Currentmember).Parent.Lastsibling.Lastchild, measure.[Balance])

2) If you drill down to the month level you can obtain the ending balance of the previous year like this:

(ParallelPeriod([Year],1,[Dim Time].Currentmember).Lastsibling.Lastchild, measure.[Balance])

3) If you are on the year level you can obtain the ending balance of the previous year like this:

([Dim Time].currentmember.Lag(1).Lastchild.Lastchild, measure.[Balance])

And finally you can put the three sentences in two nested iif function, like this:

Iif([DimTime].currentmember.Level.Name = "Day", <Sentence 1>,

Iif([DimTime].currentmember.Level.Name = "Month", <Sentence 2>,

<Sentence 3>))

Other ways using custom rollup formulas can be used.

Hope that helps you

Leandro

|||

What I think you are looking for is the new semi-additive measures in AS2K5, specifically the "last non-empty child" aggregation function. It is designed for balances, quantities on-hand, and a whole class of semi-additive situatons. While this is technically possible on AS2K, it involves a considerable amount of calculations and MDX to evaluate it at runtime. In AS2K5, it is a native aggregation function and performs at the same speed as sum, count, etc.

_-_-_ Dave

|||

That' right.

So the following more simple sentence could be used:

(parallelperiod([Year],1,[DimTime].currentmember), measures.[Balance])

Thanks