3 Replies Latest reply: Jun 10, 2016 6:51 AM by Sunny Talwar

# Use calculated value into new calculation

To create a forecasted revenue I have the following calculation.

num(SUM({1<MonthNum ={"<=\$(=Num(Month(Today())))"}, Year={"\$(=Year(Today()))"}>}[Revenue])

/ SUM({1<MonthNum ={"<=\$(=Num(Month(Today())))"}, Year={"\$(=Year(Today())-1)"}>}[Revenue])-1,'#,##0.0%')

This gives a ratio of this year YTD revenue vs last year YTD revenue. Now I want to use that ratio into a calculation about 2016.

Say [actual revenue] *

num(SUM({1<MonthNum ={"<=\$(=Num(Month(Today())))"}, Year={"\$(=Year(Today()))"}>}[Revenue])

/ SUM({1<MonthNum ={"<=\$(=Num(Month(Today())))"}, Year={"\$(=Year(Today())-1)"}>}[Revenue])-1,'#,##0.0%')

However, if I plot this in a table with a year dimension I get nothing.

• ###### Re: Use calculated value into new calculation

May be try this:

[actual revenue] *

If(Year = 2016, Num(Sum(TOTAL {1<MonthNum ={"<=\$(=Num(Month(Today())))"}, Year={"\$(=Year(Today()))"}>}[Revenue])/Sum(TOTAL {1<MonthNum ={"<=\$(=Num(Month(Today())))"}, Year={"\$(=Year(Today())-1)"}>}[Revenue])-1, 1),'#,##0.0%')

I have used a static condition of Year = 2016, but you need to determine a more advanced condition for what determines a forecast here?

• ###### Re: Use calculated value into new calculation

Thanks! The Total was the key here!

• ###### Re: Use calculated value into new calculation

Indeed it was