GETPIVOTDATA Formula

[southcali12]

Hi,

I am trying to use the GETPIVOTDATA formula to fill in the table below:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]12/7/2015
[/TD]
[TD]12/8/2015
[/TD]
[TD]12/9/2015
[/TD]
[TD]12/10/2015
[/TD]
[TD]12/11/2015
[/TD]
[/TR]
[TR]
[TD]Sales
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My pivot table looks like such:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD]12/7/2015
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]12/8/2015
[/TD]
[TD]120
[/TD]
[/TR]
[TR]
[TD]12/9/2015
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]12/10/2015
[/TD]
[TD]500
[/TD]
[/TR]
</tbody>[/TABLE]

The formula I have written is:
=GETPIVOTDATA("Sales",$A$3,"Date",H1)
So for example $A$3 is where the Date is located in the pivot table and H1 is the date.

Any advice would be greatly appreciated. Thank you!

 

[jorismoerings]

Hi,

From the little info give, your formula looks OK. Could you explain what your issue is...does your formula return an error or ...

 

[southcali12]

Hi thanks for your response, yes it returns #REF

 

[southcali12]

I'm not sure what else could be wrong. The pivot table is in tabular form, I don't know if that would matter? Or that the Sales is in the value field.

 

[RoryA]

What formula is generated if you type = and then select the cell you want in the pivot table?

 

[jorismoerings]

If the formula would return no result, the error would be #VALUE. This #REF error is probably caused by an fault in the formula. My guess would be to check the format of the date in cell H1 and ensure it has same format as within your data.

 

[southcali12]

Hi thank you for your response the formula that is generated is:
=GETPIVOTDATA("[Measures].[Sales]",$B$3,"[RawData].[Date]","[RawData].[Date].&[2015-11-23T00:00:00]")

OH. I see the problem now! And please correct me if I'm wrong, but as I added my data into the Powerpivot, the date got converted to be yyyy-mm-ddThh:mm:ss (even though I reformatted it to be m-dd-yyyy). So if I want to change the date in the formula above, I would have to add in the =TEXT formula to the =GETPIVOTDATA formula?

 

[RoryA]

You'll need something like:

=GETPIVOTDATA("[Measures].[Sales]",$B$3,"[RawData].[Date]","[RawData].[Date].&["&TEXT(H1,"yyyy-mm-dd")&"T00:00:00]")

 

[southcali12]

Thank you so much Rory, you're a genius!

 

[RoryA]

Glad to help.