GetPivotData gives #REF error if record doesn't exist

[ExcelAtEverything]

I'm having a problem which I'm not sure how to resolve..

The following GETPIVOTDATA formula is designed to grab the appropriate number of "Returns" processed for a given date. It normally works perfectly, but I noticed that every now and again it was throwing #REF errors (depending on the date). What's happening is this: Returns don't happen every day. Some days there will be none. On those "0 Return" days, the report isn't generating a record showing a "0". Instead, it's simply not generating anything for that day. So then when my Pivot formula attempts to locate it to return the number", there is no record and I get a #REF error.

The data list for this Pivot Table will grow every day as new sales records get loaded each day for the prior day. The Pivot Table itsef was created from an Excel doc sitting in a folder on my Desktop, which I accessed via Power Query>Data Model>Pivot Table.

How can I correct this? Thanks!

=GETPIVOTDATA("[Measures].[Sum of Quantity 2]",'R&E Yearly'!$B$5,
"[Order Returns Report].[Org #]","[Order Returns Report].[Org #].&[" &[@[Org '#]]& "]",
"[Order Returns Report].[Date]","[Order Returns Report].[Date].&["&TEXT($G$3-1,"yyyy-mm-dd") & "T00:00:00]",
"[Order Returns Report].[Date (Year)]","[Order Returns Report].[Date (Year)].&"&"["&YEAR($G$3-1)&"]")

 

[RoryA]

That's how GETPIVOTDATA works. You need to wrap it in an IFERROR function:

=IFERROR(your formula,"no record")

for example.

 

[ExcelAtEverything]

RoryA, About 90 seconds ago I had just got finished eating & sat back down at my laptop to ponder the issue with a refreshed perspective. I looked at the formula and almost immediately laughed at myself and said "wtf am I talking about, I forgot the IFERROR on this column". I them immediately thought of this posting and was like "Why of why didn't I wait until AFTERwards to post this question"? Embarassing. LOL!!! Thanks so much Rory!

 

[ExcelAtEverything]

That's how GETPIVOTDATA works. You need to wrap it in an IFERROR function:

=IFERROR(your formula,"no record")

for example.

I spoke too soon, RoryA. That doesn't quite address the problem, but I'm afraid (my apologies?) that I forgot a layer of detail when posting my question. The reason I asked the question was because the IFERROR formula doesn't solve the issue for the columns shown in the screenshot, because the screenshot contains running totals L to R.
--->I used Running Totals for MTD & YTD Tables for this report, because that works best for me in pulling the info.
--->I left Running Totals OFF for the "Yesterday" table in this report. IFERROR resolves the issue for "Yesterday", but my question was regarding the other 2 tables.
Because there is no record being generated by the original report on 3/28, the Pivot Table shows nothing on the 28th, and the running total picks up again on 3/29 when the next record was generated. This seems to be the reason I am getting a #REF error when I set my report date to look at any dates where there were zero records (like the 28th).

So obviously, plugging in the IFERROR on this will just mask the problem. If a daily record is empty, then I still need that running total as it would appear on the 28th if there were a record present. Again, the formula works perfectly when there is a record present like on the 27th ot the 29th. I need a way to maybe add something to the formula where if it encounters no record there then it will look to the last number recorded prior to that... or maybe there's a better solution altogether?

Thx, and sorry for forgetting this in my initial post.?

 

[ExcelAtEverything]

I spoke too soon, RoryA. That doesn't quite address the problem, but I'm afraid (my apologies?) that I forgot a layer of detail when posting my question. The reason I asked the question was because the IFERROR formula doesn't solve the issue for the columns shown in the screenshot, because the screenshot contains running totals L to R.
--->I used Running Totals for MTD & YTD Tables for this report, because that works best for me in pulling the info.
--->I left Running Totals OFF for the "Yesterday" table in this report. IFERROR resolves the issue for "Yesterday", but my question was regarding the other 2 tables.
Because there is no record being generated by the original report on 3/28, the Pivot Table shows nothing on the 28th, and the running total picks up again on 3/29 when the next record was generated. This seems to be the reason I am getting a #REF error when I set my report date to look at any dates where there were zero records (like the 28th).

So obviously, plugging in the IFERROR on this will just mask the problem. If a daily record is empty, then I still need that running total as it would appear on the 28th if there were a record present. Again, the formula works perfectly when there is a record present like on the 27th ot the 29th. I need a way to maybe add something to the formula where if it encounters no record there then it will look to the last number recorded prior to that... or maybe there's a better solution altogether?

Thx, and sorry for forgetting this in my initial post.?

I think I have it figured out! And it only took me...well... all day! This basicaly checks for #REF error, and if no error then it runs the normal GETPIVOTDATA formula I had in there to begin with. However if a #REF error does occur (which was occurring when there was a zero for that day & therefore no record in the Pivot Table), then it runs the SUMIF formula which sums all the records for each date in the exploded Pivot Table (but only for dates that occur on or after the first day of the month of the report date, and before the actual report date). now I just need to translate it into my YTD table and I'll be all good. It tok a little wiggling with the formula since the report date changes all the time, and I needed to account for the month changeovers as well.

=IFERROR(GETPIVOTDATA("[__Xl2].[Measures].[Sum of Quantity 2]",'R&E Daily & Monthly'!$B$5,
"[Order Returns Report].[Org #]","[Order Returns Report].[Org #].&[" &[@[Org '#]]& "]",
"[Order Returns Report].[Date]","[Order Returns Report].[Date].&["&TEXT($G$3-1,"yyyy-mm-dd") & "T00:00:00]",
"[Order Returns Report].[Date (Month)]","[Order Returns Report].[Date (Month)].&[" & TEXT($G$3-1,"mmm") & "]"),
SUMIFS('R&E Daily & Monthly'!C13:ED13,'R&E Daily & Monthly'!$C$7:$ED$7,"<"&MOR!$G$3,'R&E Daily & Monthly'!$C$7:$ED$7,">="&EOMONTH(MOR!$G$3-1,-1)+1))