CUBEVALUE + Named Value

[SimonNU]

Hi Guys

I'm having issues feeding a named cell into the CUBEMEMBER formula. I would like the below to insert a named range iCurrentPeriod in the place of "11". Can you please advise?

=CUBEMEMBER("PowerPivot Data",{"[UTILIZATION SHEETS].[Period].&[11]","[Measures].[Sum of FTE]"},"ACTUALS")

=

=CUBEMEMBER("PowerPivot Data",{"[UTILIZATION SHEETS].[Period].&[[B]iCurrentPeriod[/B]]","[Measures].[Sum of FTE]"},"ACTUALS")

 

[ameya]

Could you use an if statement to say something like if there is an 11, insert iCurrentPeriod? Just a suggestion, not sure how CubeValue works.

 

[SimonNU]

Oops, the title reads CUBEVALUE... It is supposed to read CUBEMEMBER.

The question is more of an Excel based question as opposed to a PP one I guess...

No if statement should be required. The solution should be something like this but I can't get it working:


=CUBEMEMBER("PowerPivot Data",{"[UTILIZATION SHEETS].[Period].&[" & iCurrentPeriod & "]","[Measures].[Sum of FTE]"},"ACTUALS")</pre>

 

[Tianbas]

Try to avoid the {} and separate it in two formulas. One Formula only for the period and one for the Sum of FTE. In your CUBEVALUE formula you can refer to the cell with the period fomula. Change that period cell and your CUBEVALUE will be updated.

Sometimes it also works if you just enter the correct name of the period in that cell. As an example I have a report with following CUBEVALUE formula:

=CUBEVALUE("PowerPivot Data";$A$1;$A$2)
A1 includes a CUBEMEMBER formula =CUBEMEMBER("PowerPivot Data";"[Measures].[Sum of XYZ]")
A2 was the following formula in the beginning =CUBEMEMBER("PowerPivot Data";"[Period].[Month].&[Jul]") until I realized that the CUBEVALUE formula also updates if I just enter Jul in cell A2