Using Excel CubeValue function with date range

• Jonathan Mallia

  SSCertifiable

  Points: 5192

  More actions

  November 27, 2015 at 2:19 am

  #322929

  Hi everyone,

  Our end users are using Excel, and they need write 2 dates to act as a date range (for example: 2015-03-13 in one cell and 2015-05-24 in another cell).

  These values would need to be passed on to a CUBEVALUE function to return a measure value, for example Sales for the specified period.

  Has anyone ever used CUBEVALUE function in Excel, specifying a date range? I know this can be done using the Date filter but it is not very user-friendly as the slicer will be large.

  =CUBEVALUE("CubeConn", "[Measures].[Sales]", "[Date].[Full Date].[" & C2 & "]")

  Thanks for your advice.

  Jon

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  November 28, 2015 at 8:04 am

  #1842712

  Does your expression currently work for one date value?

  The MDX syntax for a date range is this: <date1> : <date2>

  You should be able to use this in your CUBEVALUE function, as long as it is passed in the same member expression parameter. I haven't tried this before, but if your function works for one date it should merely be a matter of getting the syntax right. Also use a trace to see what MDX is generated by Excel... that could help you troubleshoot the issue (if any).