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]

Are you saying that you think I need Power BI in order to do what I'm asking?

 

[ExcelAtEverything]

If that's the case, then I will need to abort the manual-entry date process, and revert back to the =today() method.

 

[Alex Blakenburg]

Are you saying that you think I need Power BI in order to do what I'm asking?

Not at all. Only that the experts in using the Data Model will most likely also be the Power BI experts. I don't know if they would look at both the Excel Questions Forum AND the Power BI Forum. Just give it a day or so and see if anyone responds.

 

[ExcelAtEverything]

Gotcha. Many thanks.

 

[ExcelAtEverything]

Mynda gives a good explanation and it would be worth your while having a read.
https://www.myonlinetraininghub.com/getpivotdata-function-power-pivot

Not sure why I thought you were referring to a YouTube video here. Man, I was out of it! LMAO!
Anyway, thank you because this is a GREAT read! So much useful info!

 

[ExcelAtEverything]

Alex, I just wanted to say that the advice you gave me in this thread regarding Pivot Tables and the GetPivotData formula is absolutely invaluable. I can't even tell you how often I have referred back to it over the last several days. From that alone (specifically the part about separating the formula onto different lines, and just replacing parts of it. Just superb. This thread, along with an exceptiponal 2hr video I found on Pivot Tables, has made me SO much better of the past few days. I'm having a blast rewriting these formulas, and making it work. Thanks so much for taking the time.

 

[Alex Blakenburg]

Alex, I just wanted to say that the advice you gave me in this thread regarding Pivot Tables and the GetPivotData formula is absolutely invaluable. I can't even tell you how often I have referred back to it over the last several days. From that alone (specifically the part about separating the formula onto different lines, and just replacing parts of it. Just superb. This thread, along with an exceptiponal 2hr video I found on Pivot Tables, has made me SO much better of the past few days. I'm having a blast rewriting these formulas, and making it work. Thanks so much for taking the time.

You've made my day. Thank you.

 

[ExcelAtEverything]

Bro.... Stop it. You should make a tutorial. So many helpful and incredibly knowledgable people on this site, and so many have helped me to understand things when I get confused. But the way which you explain things is on a different level. I went from needing help with so many questions each day, and I was so lost in that formula. By the next day around noon, I felt like a whiz at it. For as long as it took me to get all of that help from people that I requested regarding PT's and that GETPIVOTDATA formula, I ended up erasing all of it and redoing the, all myself, simply because I couldn't be sure they were 100% correct if someone else did them. So instead of going back and analyzing everyone's formula, I decided to redo every single cell myself. I now feel so much more in control of the project.

Ok, I gotta stop now before your head gets too big. But I'm serious man. You have no idea how much you completely cleaned the cobwebs for me. I'm sure I'll need a lot more help still, but I feel so much more confident that the sheet has not gotten away from me at all, whereas I totally felt like that before.

Thx Alex. You have a gift when it comes to teaching others.