Results 1 to 3 of 3

Thread: CUBEMEMBER / CUBEVALUE dates query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default CUBEMEMBER / CUBEVALUE dates query

    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&"]")

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: CUBEMEMBER / CUBEVALUE dates query

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

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: CUBEMEMBER / CUBEVALUE dates query

    Quote Originally Posted by RoryA View Post
    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???

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •