Use calculated value into new calculation

[juriengroot]

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.

[sunny_talwar · Accepted solution]

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?

[juriengroot]

Thanks! The Total was the key here!

[sunny_talwar]

Indeed it was