My data model has a column named rowDate. This column has several repeating instances of dates due to the fact that there are multiple rows of data for different times of a single day.
I've written a measure that sums the values in another column from the data model that I want to pull from. (We'll call this MyMeasure.)
I'm trying to write a CUBEVALUE formula that extracts the total of MyMeasure when rowDate equals the value of a cell in a worksheet. (This will allow the result of the CUBEVALUE to change based on the date that the user inputs into cell B2.)
e.g.
Cell B2 = "2018-06-29" (date formatted)
=CUBEVALUE("ThisWorkbookDataModel","[MyMeasure]","[Summary Data].[rowDate].["&$B$2&"]")
This always returns #N/A. I have another column with data (a number) that I can write the exact same CUBEVALUE formula, but referencing the appropriate cell, and it works just fine. It's just this date column that I can't figure out.
Found the solution in this thread. The trick is to format the date in your targeted cell inline using the TEXT function to match the cube's default date format (YYYY-MM-DDTHH:MM:SS).
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[MyMeasure]","[Summary Data].[rowDate].&["&TEXT($B$2,"YYYY-MM-DDTHH:MM:SS")&"]")