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!

 

[Alex Blakenburg]

Whoa! That worked great!???

Doing your first ever GetPivot formula using the Data Model & Power Pivot was certainly throwing yourself in the deep end.
Even if Mynda Treacy from MyOnlineTrainingHub says:-
"Don’t despair because writing relative Power Pivot GETPIVOTDATA formulas is easy.
All we need to do is replace the hard-keyed argument with a reference to the cell containing the province, so that when it’s copied down it picks up the next province."
(province is similar to your Org # in her example)

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

Because the replacing of constants is such an exact process, its worth doing only 1 replacement at at time and testing that it works along the way.

A picture of my steps for replacing the 2021 Year with a variable are below:

(The date component is usually the trickiest one but the process is the same as the below its just that you will need the Text(cell,format) function to get the format to match the format that the pivot table is using)

 

[ExcelAtEverything]

Yes! Her videos basically taught me Power Query a few weeks ago! She's great, so I'll definitely check that out. I've watched quite a few already, and it's very possibe that I've already seen this one. But I find that sometimes while watching these videos too early in my learning proces, I don't recall almost as much as I do, but it's still very helpful... then I just revisit them again and again as my level of understanding grows. I've been looking very hard on YT and just online in general for any sort of instrruction on learning how to effectivel yunderstand how to change that formula, terminology, etc, but still haven't come accross anything.

So now I almost have all 18 of my "YESTERDAY" table info cell formulas rewritten and working. I just started on the Month To Date row and so still trying to figure that out. But looks like you just sent an example for the YTD table, is that correct?

 

[Alex Blakenburg]

looks like you just sent an example for the YTD table, is that correct?

It's just showing you how to go about replacing the constants. To make it specific for YTD,
• you need to point the first cell reference in the formula to your YTD Pivot cell in top left corner.
• remove the Date criteria line
• remove the Month criteria line

 

[Alex Blakenburg]

Isn't after 2am there ?

 

[ExcelAtEverything]

Isn't after 2am there ?

Yep. Once or twice a week I'll stay up for 2 days straight, then sleep for about 10 hrs. I'm currently on hour #39 of being up.

 

[ExcelAtEverything]

Welp, that's it for me tonight. Off for my 10 hour hibernation. Lol
Based on my date being set as 4/2/2021 at $G$3, this is my starting MTD formula (no adjustments made yet):
=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]","[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[Apr]")

And this is my starting YTD formula (again, no adjustments made yet):
=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]","[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[Apr]")

And this is what mynew YTD GS Pivot Table looks like. Everything is exploded.

 

[ExcelAtEverything]

I completely misread your message ast night. I thought you sent a new formula for the year-to-date GS. I just reread it and see that you were showing me your steps for making 2021 a variable, which is SUPER-helpful. Thank you! I was definitely far past the point where I should have went to sleep last night. Lol

 

[ExcelAtEverything]

Hi Alex! I actually just posted a new question to the site, which is a follow up to our conversation here, and is directly related. I thought it best to start a different thread though, since it's a new thing I need solved, rather than just piling new questions into the same topic. I'll mark this one complete, but still may jump in and out of it as needed. Thanks! And here is the new question if you wish to continue! You've been amazingly helpful!!! ? ? ?

Changing GETPIVOTDATA to add all sales in a month up to the date shown in a cell

Hello, My project is a yearly/monthly/daily sales tracker. All of my data is located in Excel sheets kept in different folders which are all kept on my desktop, and the following question is based off of info from 1 of those folders named "Monthly Orders Reports". The info is accessed by my...

www.mrexcel.com

 

[ExcelAtEverything]

Looks like I already marked this one as solved. Slipped my mind.

 

[Alex Blakenburg]

Hi Alex! I actually just posted a new question to the site, which is a follow up to our conversation here, and is directly related. I thought it best to start a different thread though, since it's a new thing I need solved, rather than just piling new questions into the same topic. I'll mark this one complete, but still may jump in and out of it as needed. Thanks! And here is the new question if you wish to continue! You've been amazingly helpful!!! ? ? ?

Changing GETPIVOTDATA to add all sales in a month up to the date shown in a cell

Hello, My project is a yearly/monthly/daily sales tracker. All of my data is located in Excel sheets kept in different folders which are all kept on my desktop, and the following question is based off of info from 1 of those folders named "Monthly Orders Reports". The info is accessed by my...

www.mrexcel.com

Sounds good. The link was handy. I had a look. Its not something I know of the top of my head, so I will leave it for someone else to answer at this stage.
The new thread is starting to overlap into Power BI territory for which there is a separate stream in the forum, so hopefully someone looks at both streams.