0

I am using the CubeValue function in Excel Powerpivot (2013) and would like to know if it is possible to use operators in the MemberExpression, particularly to retrieve values less than or greater than, e.g:

=CUBEVALUE("PowerPivot Data","[Measures].[CountofServiceID]","[Data].[ReportMonthNumber].&[<"&$P$4&"]")

The syntax above (<) is incorrect and I would like to know how to do this?

Thanks

MGee
  • 1
  • 5

1 Answers1

0

In general, I find it is easier to break cube function formulas up and use cubevalue to simply reference other fields. You can create a cubeset to do what you need.

If we put this is cell A2: 

=cubemember("PowerPivot Data", "[Measures].[CountofServiceID]")

In some other cell, let's say A3, use this formula: 

=Cubeset("PowerPivot Data","{[Data].[ReportMonthNumber].[All].[1]:[Data].[ReportMonthNumber].[All].["& $P$4 & "].lag(1)}"," Reporting Months")

The cell that should contain your value would be: 

=cubevalue("PowerPivot Data", A2, A3)
mmarie
  • 5,598
  • 1
  • 18
  • 33