How to calculate the previous year YTD balance in profit & loss statement

[Former Member]

Dear all,

I would like to seek for advice on how to calculate the previous year YTD (Year to date) balance in profit & loss statement

For example, if I enter May 2009, the YTD value should be a sum up of value from Jan to May.

I have tried the current year YTD could be set in column selection dimension "financial period"

FACT PER( Code = YearFirst(@Per):@Per ) Order By PER.Code Descending

Then, I tried the previous year YTD in another column using

FACT PER( Code = YearFirst(@Per-12):@Per-12 ) Order By PER.Code Descending

But it failed -> Abnormally display three column, while the financial period I enter 200903 in parameter @Per

Would anyone kindly help me on that?

Regards,

Simon Chiu

[Former Member · Accepted solution]

There are a couple of ways.

One is YTD(@Per-12)

The other is using your Yearfirst but change it to YearFirst(@Per)-12 so it calc the Yearfirst and then substracts the months. You can use the syntax wizard to help you with these calcs.

Jim

[Former Member · Accepted solution]

Dear Jim,

Thanks for your reply.

However, the problems still cannot be solved.

My expected output is 1 column listing the YTD value from previous year.

I have tried two times using both the Code = YTD(@Per-12) and Code = YearFirst(@Per)-12:@Per respectively. However, the Excel display various column.

I am using Financial Period 200903. In the last three column, it display the data in Financial Period 200901, 200902 and 200903 respectively.

For the current year, the YTD is correct and show 1 column listing the sum of account in the Financial Period 200901, 200902 and 200903

Would you kindly advice the code setting and how to set? Also, how I can access the syntax builder?

Regards,

Simon Chiu

[Former Member · Accepted solution]

YTD(@per-12) is correct. When you apply your parameter if you click once in the value section you will see a Dimension lookup for the Sytnax wizard.

You can check this using the drilldown function.

I would recommend to copy your working YTD column by right click and copy and paste and then change it to YTD(@per-12) and than click apply.

Jim