Dynamic GETPIVOTDATA( ) returns #REF!

Question

Greetings,

I can't seem to get this formula to work. I'm using PowerPivot and trying to make the formula dynamic.

I use = to set a cell to the value in a pivot table on another sheet. This works fine but doesn't update when the pivot table updates.

=GETPIVOTDATA("[Measures].[Sum of slots_mdr]",TEST_TAT!$A$4,"[rawTAT].[appt_dt]","[rawTAT].[appt_dt].&[2021-03-26T00:00:00]","[rawTAT].[status]","[rawTAT].[status].&[BOOKED]")

I need that "2021-03-26T00:00:00" to reflect the date in cell C8 on the same sheet. I have tried these formulas as well as a few more:

=GETPIVOTDATA("[Measures].[Sum of slots_mdr]",TEST_TAT!$A$4,"[rawTAT].[appt_dt]",TEXT(C$8, "yyyy-mm-dd") & "T00:00:00","[rawTAT].[status]","[rawTAT].[status].&[BOOKED]")

=GETPIVOTDATA("[Measures].[Sum of slots_mdr]",TEST_TAT!$A$4,"[rawTAT].[appt_dt]","[rawTAT].[appt_dt].&["&TEXT(D8,"mm/dd/yyyy")&"]","[rawTAT].[status]","[rawTAT].[status].&[BOOKED]")

When I use regular Pivot tables I can just use C8 in the formula and it works fine. I have a row full of dates and the corresponding pivot table values right below them, all updating when I change the slicers that control the pivot table. Just cant seem to get it working with PowerPivot.

Please help!

Answer 1

Solved it. I was just not concatenating the string properly in the GETPIVOTDATA function. Corrected to:

GETPIVOTDATA("[Measures].[Sum of slots_mdr]",TEST_TAT!$A$4,"[rawTAT].[appt_dt]","[rawTAT].[appt_dt].&["&TEXT(C$8, "yyyy-mm-dd")&"T00:00:00]","[rawTAT].[status]","[rawTAT].[status].&[BOOKED]")

Subtle difference but enough to break it. I was missing a closing bracket.