Adjusting GETPIVOTDATA Formula To Be Dynamic

[ExcelAtEverything]

I am on the Year To Date (YTD tab now, and stuck on the same thing When I grab the static formula for the Pivot Table position, I need to adjust the Org # portion so the formula can be pulled downward and will translate properly to the diffferent Org #'s, and same holds true for date needing to be able to be pulled to right, as well as account for the fact that next year will be a new year. I'm trying very hard, but I'm having a tough time trying to grasp certain aspects of the GETPIVOTDATA function. Any help here greatly appreciated as always.

Green/White table w/ blacked out names: this table (named "YTD_GS") is the main sheet where the GETPIVOTDATA formulas go. Sheet name is "Main".
Black/white Pivot Table:

If I just grab the static Power Pivot data cell, the formula looks like this: =GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]","[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&[2021]")
And it fills down incorrectly like this.

.

I tried editing it myself and came up with this, which seems right to me, but it only throws #REF errors.
=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&["&[@[Org '#]]&"]","[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Month)].&["&TEXT(TODAY()-1,"mmm")&"]")

Thanks!

 

[ExcelAtEverything]

C8:C11

C8

=GETPIVOTDATA("Line Item total",'Monthly Orders Reports'!$A$30,"Org #",[@[Org '#]],"Date",$B$2-DATE(YEAR($B$2),1,0)+1,"Months",MONTH($B$2),"Years",YEAR($B$2))

I was messing around with your formula, trying to make sense of it as best I can, and I tried this in my Gross Sales / Yesterday cell, but ended up with a #REF error.
=GETPIVOTDATA("[Sum of Line Item Total]",'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[@[Org '#]]","Date",$G$3-DATE(YEAR($G$3),1,0)+1,"Months",MONTH($G$3),"Years",YEAR($G$3))
**I have my date in $G$3.

 

[Alex Blakenburg]

Do 1 substitution at at a time then test.

Try this, I removed the [Monthly Orders Reports]. prefix before the [@[Org '#]] and removed the quotes.

=GETPIVOTDATA("[Sum of Line Item Total]",
'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]",[@[Org '#]],
"Date",$G$3-DATE(YEAR($G$3),1,0)+1,
"Months",MONTH($G$3),
"Years",YEAR($G$3))

 

[ExcelAtEverything]

A) You're supposed to be sleeping!
B) So I can just remove that? Eveyone else's GETPIVOTDATA formulas seem so short and clean, but mine always load with all these folder names & table names. If I remove it does it just assume something instead?

 

[Alex Blakenburg]

Also you can't "mess" with formula.
You have to start with yours and substitute.
Follow my method screenshot.

Eg since your pivot field reference for each field had in the original and still has for the org, a prefix of [Monthly Orders Reports] its likely your Date Mont & Year pivot field references should have those.

You find your formula easier to read if you set the cell to wrap and hit alt+enter before/after each dimension/ criteria pair so it looks like my post just before this one. (Effectively 5 lines)

 

[Alex Blakenburg]

A) You're supposed to be sleeping!
B) So I can just remove that? Eveyone else's GETPIVOTDATA formulas seem so short and clean, but mine always load with all these folder names & table names. If I remove it does it just assume something instead?

See my previous post.
It's likely they are not using "Power Pivot" and that it's Power Pivot that needs the prefix [Monthly Orders Reports].

 

[ExcelAtEverything]

I'm not even sure why I'm using it to be honest with you. When you said that a few posts ago about mine being in Power Pivot, not only was I unaware of it... but I had no idea what the difference was. Then when I looked it up, I still am not sure how or why I ended up do it in Power Pivot. Sounds like the reason to do so would be to combine data from different queries into the same data model, but I didn't do that. I just inserted a Pivot Table. Also I checked my data model, and there are zero relationships anywhere.

 

[ExcelAtEverything]

Also you can't "mess" with formula.
You have to start with yours and substitute.
Follow my method screenshot.

Eg since your pivot field reference for each field had in the original and still has for the org, a prefix of [Monthly Orders Reports] its likely your Date Mont & Year pivot field references should have those.

You find your formula easier to read if you set the cell to wrap and hit alt+enter before/after each dimension/ criteria pair so it looks like my post just before this one. (Effectively 5 lines)

Great pointers, and I will. Thx

 

[ExcelAtEverything]

I'm having trouble understanding these two. Change the 95, and change the 4? Where are you seeing those? I assume this is just something on your sheet because you don't have all of my details, but then which part would it replace on mine?

***Note: This is what my initial static formula looks like: As you can see I've already starting trying to adjust the base formula using your approach. I started with the Org #, but I'm still getting a #REF error on that part (in red below).

=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,
"[Monthly Orders Reports].[Org #]",[Org #].&[@[Org '#]],
"[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&[2021]",
"[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[Apr]",
"[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&[2021-04-02T00:00:00]")

 

[Alex Blakenburg]

I'm having trouble understanding these two. Change the 95, and change the 4? Where are you seeing those? I assume this is just something on your sheet because you don't have all of my details, but then which part would it replace on mine?

***Note: This is what my initial static formula looks like: As you can see I've already starting trying to adjust the base formula using your approach. I started with the Org #, but I'm still getting a #REF error on that part (in red below).

=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,
"[Monthly Orders Reports].[Org #]",[Org #].&[@[Org '#]],
"[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&[2021]",
"[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[Apr]",
"[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&[2021-04-02T00:00:00]")

Based on the information to date I have tried to replicate the Data Model and Power Pivot enough to get identical field names.
I am using your G3 for the date.

Drop this into your Table with the Org # field in it where you have been putting the formulas and see if it work just as it is.
I know its getting late in DC but just in case you still wanted to try it today.
I will try to explain if it works for you.

=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,
"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" &[@[Org '#]]& "]",
"[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&["&TEXT($G$3,"yyyy-mm-dd") & "T00:00:00]",
"[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[" & TEXT($G$3,"mmm") & "]",
"[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&"&"["&YEAR($G$3)&"]")

 

[ExcelAtEverything]

=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3, "[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" &[@[Org '#]]& "]", "[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&["&TEXT($G$3,"yyyy-mm-dd") & "T00:00:00]", "[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[" & TEXT($G$3,"mmm") & "]", "[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&"&"["&YEAR($G$3)&"]")

Whoa! That worked great!???