+ Reply to Thread
Results 1 to 3 of 3

GETPIVOTDATA() with a pivot table from a data model

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    GETPIVOTDATA() with a pivot table from a data model

    I am trying to understand how to use GETPIVOTDATA() when it is used with a pivot table from a data model.

    I have a table that looks like this;

    Date Count of LTC
    27/04/2021 17026
    28/04/2021 24621
    29/04/2021 23201
    30/04/2021 12878

    The date column comes from a data model table (DataModelTable) with a column called UPDATE_TIME. The Count of LTC column comes from the same table in the data model.

    What I want to be able to do is have a cell elsewhere on my worksheet where a date can be entered, and use GETPIVOTDATA to retrieve the 'Count of LTC' value. I have used GETPIVOTDATA() to get the data I want by clicking on cells and get this formula.

    =GETPIVOTDATA("[Measures].[Count of LTC]",$B$4,"[DataModelTable].[UPDATE_DATETIME]","[DataModelTable].[UPDATE_DATETIME].&[2021-04-27T00:00:00]")

    The first cell in my Pivot Table is at cell B4. The above of course works. Using this as a template of the formula I need I have created this....

    =GETPIVOTDATA("[Measures].[Count of LTC]",$B$4,"[DataModelTable].[UPDATE_DATETIME]",I25)

    Cell I25 has a date in it (27/04/21). The result I get back is a '#REF!' error. The only difference as I see it is the way that I have expressed the date. The date in the Pivot Table (Data Model) is a full date timestamp. This makes me think that I need to expand my date in I25 to a full timestamp. If so, how can I do that?

    Any guidance, suggestions or solutions will be very welcome.

    TIA

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: GETPIVOTDATA() with a pivot table from a data model

    assuming I25 holds a date (no time) then:

    =GETPIVOTDATA("[Measures].[Count of LTC]",$B$4,"[DataModelTable].[UPDATE_DATETIME]","[DataModelTable].[UPDATE_DATETIME].&["&TEXT(I25,"YYYY-MM-DD")&"T00:00:00]")

  3. #3
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: GETPIVOTDATA() with a pivot table from a data model

    Thanks for this. I was coming to this conclusion from other research I have done. What you have proposed works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] GETPIVOTDATA Problem (Data Model)
    By troilii in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 03-23-2021, 01:01 AM
  2. How to GetPivotData from Data Model Pivot Table
    By tb426 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-20-2019, 12:43 PM
  3. Replies: 3
    Last Post: 07-12-2019, 06:17 AM
  4. GETPIVOTDATA from Data model Pivot Table
    By triads in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-29-2019, 11:40 AM
  5. For each pivot item in pivot table - for data model
    By dancing-shadow in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-23-2017, 02:45 PM
  6. Creating a Pivot Table from Power Pivot Data Model?
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2017, 12:02 PM
  7. [SOLVED] GetPivotData but with data model
    By rodich in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2016, 06:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1