CUBEMEMBER / CUBEVALUE dates query

[BakerUK]

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]

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

 

[BakerUK]

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???