Variance P&L pivot

[dion1999]

Hi there, I was trying to calculate simple  variance between current year and last year. 

This is my expression:

(Sum({$<FYEAR={'$(=Max(FYEAR))'}>} [IS.Budget_Amt]) - Sum({$<FYEAR={'$(=Max(FYEAR)-1)'}>} [IS.Budget_Amt]))/Sum({$<FYEAR={'$(=Max(FYEAR)-1)'}>} [IS.Budget_Amt])

I wasn't getting expected results. Can someone tell me what's wrong.

[rbartley]

Hi,

Try:

(Sum(TOTAL {$<FYEAR={'$(=Max(FYEAR))'}>} [IS.Budget_Amt]) - Sum(TOTAL {$<FYEAR={'$(=Max(FYEAR)-1)'}>} [IS.Budget_Amt]))/Sum(TOTAL {$<FYEAR={'$(=Max(FYEAR)-1)'}>} [IS.Budget_Amt])

[dion1999]

Hi, thanks for helping, but the results are not correct.

This is what the data model look like. I feel like the problem might be on the data model side.  To present the data on P&L pivot extension, I have to group by and concatenate Budget amount with its two granulity of group. For example, Revenue | Net Course Income | $100, in the IS table, will have two records,  Revenue | $100 and Net Course Income | $100.

I hope my bad explanation can help you understand.

[rbartley]

Please upload your app, changing the values if any are confidential.