# CUBEMEMBER / CUBEVALUE dates query



## BakerUK (Aug 20, 2019)

Hi, I am trying to lookup a Power Pivot dates table in native Excel with a formula to lookup the date to return financial week.

I have the date in cell K1 in the correct format.

Is there a cube formula which can lookup ThisWorkbookDataModel to return the week associated with the date in K1? So far I am stuck here which returns N/A.

*=CUBEVALUE("ThisWorkbookDataModel","[FinWeek]","[FinCalendar].[Date].["&K$1&"]")*


----------



## RoryA (Aug 20, 2019)

How would the formula look with a fixed value? You'll probably need to use the TEXT function to format K1 appropriately.


----------



## BakerUK (Aug 20, 2019)

RoryA said:


> How would the formula look with a fixed value? You'll probably need to use the TEXT function to format K1 appropriately.



It's in this format 2019-07-27T00:00:00 but I've used TEXT to convert K1 to the same format. When I use OLAP Tools to convert a power pivot to formulas the date format matches but I'm struggling to lookup against it to return the associated financial week from the data model.

As a workaround I can create a pivot table and GETPIVOTDATA formula but that means a pivot with several thousand rows slowing down the file. It feels like there should be a method with a cube formula???


----------

