How to create a calculated field formula to reference a non-pivot cell?

[cindygo]

Hi - Thanks in advance for your feedback.

I have pivot from a powerpivot table.

On the pivot tab above the pivot I have two formulas:
E4 I have =TODAY()
F4 I have =IF(MONTH(E4)>6,MONTH(E4)-6,MONTH(E4)+6), for the fiscal month. As of today, 4/7/2014 that would calculate to be 9.20.

In my pivot I have created a calculated field to forecast cost as in =([Sum of Cost]/9.20)*12. Is there a way to replace the number "10" with data in F4? OR is there another function that I can use to accomplish this within my pivot?

Thank you,
Cindy

 

[cindygo]

Any help on this??
Thanks,
Cindy

 

[Jacob Barnett]

Cindy, are you on 2010 or 2013?
Jacob

 

[cindygo]

Hi Jacob, I have 2013.
Cindy

 

[Jacob Barnett]

That's good news although I can't help directly as I am only on 2010. Basically the linked table functionality is improved in 2013 allowing you to use the technique in this article:

Linkback Tables in PowerPivot for Excel 2013 | SQLBI

This is more complex that what you are looking to do but hopefully it is of interest.

The alternative is to use a disconnected slicer this would mean a fixed tables of variables (in 2010 at least).

 

[cindygo]

Thanks for the response Jacob. I will look at both options and see if I can make this work. Thanks again
Cindy