Set Analysis on Previous Month

[alfredlee]

I have a simple table

Year	Month	MonthEnd Balance
2012	1	100
2012	2	200
2012	3	300
2012	4	400
2012	5	500
2012	6	600

and I would need to display the data in the following format:

Year	Month	Opening Balance	MonthEnd Balance
2012	1	0	100
2012	2	100	200
2012	3	200	300
2012	4	300	400
2012	5	400	500
2012	6	500	600

I think this is to do with the Set Analysis but I do not know how to get the Opening Balance Expression correct.

Please help and thanks in advance.

[jonathandienst · Accepted solution]

Hi

Use this for opening balance in a chart like that in your post

= Above[MonthEnd Balance]

Regards

Jonathan

[Sokkorn]

Hi Alfred,

You can try Above([MonthEnd Balance]) in your expression.

Procedure

1. Load Data

[Data]:
LOAD * INLINE [
Years,    Months,    MonthEnd Balance
2012,    1,    100
2012,    2,    200
2012,    3,    300
2012,    4,    400
2012,    5,    500
2012,    6,    600];

2. Create Straight Table with:

     a. Years and Months as Dimension

     b. Two Expression

          i. SUM([MonthEnd Balance])

          ii. Above([MonthEnd Balance])

See the sample attached file also.

Regards,

Sokkorn