8 Replies Latest reply on Sep 27, 2016 1:28 AM by lei.chen.0

Calculate growth rate on Actual + Forecast

Hello,

I'm having a dashboard with 2015 and 2016 actual revenue (Up to July) and 2016 3 months forecast (Aug, Sep, Oct) set as 2 different dimensions.

I would like to calculated the YTD growth, including the 3 months forecast.

July YTD actual + Aug/Sep/Oct forecast compared to last year Oct YTD

And ideally, the calculation should stop at Oct YTD. (As I don't have yet forecast for Nov and Dec)

I've tried to sum first the actual + forecast, then do running sum, but with no success. (Might be doing it wrong)

Any idea how to do it?

Here is a sample workbook.

Big thanks !

• 1. Re: Calculate growth rate on Actual + Forecast

Hi there

Any ideas?

Thank you !

• 2. Re: Calculate growth rate on Actual + Forecast

I still did not figure this out.

Hope you got some ideas!

Thank you

• 3. Re: Calculate growth rate on Actual + Forecast

Up ;p

• 4. Re: Calculate growth rate on Actual + Forecast

Hello Morgan,

I made a sample based on your workbook.

Please check the attached workbook for details.

Maybe because my PC local and language setting is not English,

the dateparse doesn't work out fine on my PC.

So I modified the calculation a little to get the date field.

Regards

• 5. Re: Calculate growth rate on Actual + Forecast

Thanks a lot Lei,

ZN(SUM([Value]))+ZN(SUM([Forecast].[Forecast value]))

did the trick...

I need to learn to use the ZN formula !!

Now I need to fix my second issue:

How can I stop the calculation if I don't have forecast.

Currently the YTD growth will run until the end of the selected date. But I'd like to stop it if no forecast.

Any idea on this?

Thank you

1 of 1 people found this helpful
• 6. Re: Calculate growth rate on Actual + Forecast

Hello Morgan,

I added one calculation field for example,

in which the YTD for Oct 2016 remains the same as Sep 2016.

Regards.

• 7. Re: Calculate growth rate on Actual + Forecast

This a lot Lei,

Your solution inspired me on how to tackle the issue.

It's now solved.

Thank you !

• 8. Re: Calculate growth rate on Actual + Forecast

My pleasure