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
Bookmarks