# Adjusting GETPIVOTDATA Formula To Be Dynamic



## ExcelAtEverything (Apr 4, 2021)

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 (Apr 4, 2021)

Your Org # lookup looks fine but I don't understand where you get the reference to Date(Month) from.
How did you get from this
*"[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&[2021]"*
to
*"[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Month)].&["&TEXT(TODAY()-1,"mmm")&"]"*


----------



## ExcelAtEverything (Apr 4, 2021)

Just borrowing from another formula given to me which actually did work. Again, I'm really new to Pivot Tables and don't yet fully understand the GETPIVOTDATA formula. This is an area where I'm not sure, so I tried borrowing from helpful people on this site such as yourself. There was no logic in my decision to use that except for the fact that as a final ditch effort on my part to try and make it work,I decided to try using a piece from another formula which did in fact solve that same problem. You are just seeing my most recent effort to make the formula work correctly.


----------



## Alex Blakenburg (Apr 4, 2021)

If you are happy to have the 2021 hard coded then can you try this ?
I don't have your model and on a mobile at the moment

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


----------



## ExcelAtEverything (Apr 4, 2021)

No, that's the problem. I need both Org # and Date to not be hard coded. That's what I'm having trouble doing.


----------



## Alex Blakenburg (Apr 4, 2021)

Your pIvot is only showing a Year field and your results table is not showing any date fields.
What level of Date Year or Year and month?
Where are you putting the Year and or month you are going to use to select from the pivot.


----------



## Alex Blakenburg (Apr 4, 2021)

If you were to put 2021 in E23, it should just be.

=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]",[@[Org '#]],"[Monthly Orders Reports].[Date (Year)]",$E$23)


----------



## ExcelAtEverything (Apr 4, 2021)

Alex, rather than answer your question, please allow me ask a broader question first, because it may help to get to the final answer quicker. I am completely in uncharted territory for me, as I've only been using Pivot Tables for about 2 weeks now. I started using them out of a necessity for the completion of the sheet. Here is the problem I am running into which I can't logically figure out due to my lack of upderstanding with PT's. 

My main sheet is a sales tracker which has a "Yesterday" section at the top, a "Month To Date" (MTD) section in the middle, and a "Year To Date" (YTD) section at the bottom.  I am now working on the "YTD" section. I'm a little hazy with all of the PT date functions & filtering options. If I try to just filter the Pivot Table date portion so that it only ever shows "this year", then on the 1st day of the new year, it will be blank (right?) because there isn't any info yet, and I wont receive the final 2021 data. On the other hand if I leave the Pivot Table date *unfiltered *by *not* selecting "this year", then I need to have formulas which will somehow always grab the most recent year after the years begin to accumulate. 

As for my table results, this is what I have in there


----------



## ExcelAtEverything (Apr 4, 2021)

Alex Blakenburg said:


> If you were to put 2021 in E23, it should just be.
> 
> =GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]",[@[Org '#]],"[Monthly Orders Reports].[Date (Year)]",$E$23)


But doesn't that make my sheet fragile? My sheet is going to grow exponentially. There will be more years to come, and the store list will grow significantly longer. E23 will be overwritten at some point, correct?


----------



## Alex Blakenburg (Apr 5, 2021)

You are going to tell the system what period you want somewhere.
If you don't apply a filter for a years pivot then you would get a manageable number of columns.
If you don't apply a filter to a years AND months pivot you will get number of years * no of months
At the days (yesterday) level your are going to get no of years * 365 which really becomes unwieldy.

If you go with a fully exploded Pivot it might look something like this.
(this is with a very small number of dates populated)
Also this is based on a standard Pivot Table not Power Pivot but the principle would be the same.

20210404 Power Pivot or PP GetPivot.xlsxABCDEFGHIJKLMN2930Sum of Line Item totalColumn Labels3120202020 Total20212021 TotalGrand Total32AprApr TotalFebFeb TotalMarMar TotalAprApr Total33Row Labels4-Apr4-Feb4-Mar1-Apr4-Apr342371,0201,0201,0201,0001,00010,00310,00310,04110,04420,08531,08832,108358392,0202,0202,0202,0002,00020,00320,00320,04120,04440,08562,08864,1083611933,0203,0203,0203,0003,00030,00330,00330,04130,04460,08593,08896,10837124612,02012,02012,0204,0004,00040,00340,00340,04140,04480,085124,088136,10838Grand Total18,08018,08018,08010,00010,000100,012100,012100,164100,176200,340310,352328,43239Monthly Orders Reports

If you then  wanted to pull from that Yesterday, MTD & YTD it would look something like this.

20210404 Power Pivot or PP GetPivot.xlsxABCDEF1Latest Date5/04/20212Yesterday4/04/2021345Yestderday67Org #Loc NameGS$ YesterdayMTDYTD823710,04420,08531,088983920,04440,08562,08810119330,04460,08593,08811124640,04480,085124,08812Table with getpivotCell FormulasRangeFormulaB2B2=B1-1C8:C11C8=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))D8:D11D8=GETPIVOTDATA("Line Item total",'Monthly Orders Reports'!$A$30,"Org #",[@[Org '#]],"Months",MONTH($B$2),"Years",YEAR($B$2))E8:E11E8=GETPIVOTDATA("Line Item total",'Monthly Orders Reports'!$A$30,"Org #",[@[Org '#]],"Years",YEAR($B$2))


----------



## ExcelAtEverything (Apr 4, 2021)

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 (Apr 5, 2021)

Wow Alex, this looks amazing if I can make it work! Thank you so much for taking the time to do this for me. I'm going to need to take a bit to try and ingest this, so I can better understand what I'm doing here. I have a couple of questions for you right off the bat though, if you don't mind.

1) Where are you getting* 'Monthly Orders Reports'!$A$30* from in each of these formulas?* "Monthly Orders Reports" *is the name of the Query I used in the data model for this PT, so I'm puzzled as to the significance of *$A$30* on that report. Or am I misunderstanding what it is?

2) What does "Latest Date" mean? I don't see that cell referenced in any of your formulas. I see that *B2 *is derived from *B1*, but why not just do *=TODAY()-1* in *B2*?

3) As for all the number filtering, I've been doing separate Pivot Tables (on separate tabs) for _Yesterday_, _MTD,_ & _YTD_. because that's how I saw it done in a few of the videos I watched.  So which way are you saying to do it? Have just 1 PT for all 3, and have it fully exploded? 

4) So yeah, I already have a formula to display Yesterday's date set up in *='Calc Data'!B8*. I've had a lot of problems trying to use it however, in things like Index/Match formulas as I was trying to find different ways of extracting the info I needed from the PT's, where it often times wouldn't provide a match for me. I have it formatted as a d/mm/yyyy date.

Thanks so much!


----------



## ExcelAtEverything (Apr 5, 2021)

Cancel question #2. I understand it now. That said, I don't see it referenced anywhere except in B2. So what is the purpose of B1?


----------



## Alex Blakenburg (Apr 5, 2021)

ExcelAtEverything said:


> Wow Alex, this looks amazing if I can make it work! Thank you so much for taking the time to do this for me. I'm going to need to take a bit to try and ingest this, so I can better understand what I'm doing here. I have a couple of questions for you right off the bat though, if you don't mind.
> 
> 1) Where are you getting* 'Monthly Orders Reports'!$A$30* from in each of these formulas?* "Monthly Orders Reports" *is the name of the Query I used in the data model for this PT, so I'm puzzled as to the significance of *$A$30* on that report. Or am I misunderstanding what it is?
> 
> ...



You need to remember that I don't have your spreadsheet so I have had to make up my own data and use a normal pivot and not a power pivot.
This will mean my naming is not exactly the same as yours

1) A GetPivot function needs an anchor to tell it which pivot table it is using, this is generally the top left corner of the pivot table.
If you have a look at the first spreadsheet image I have above you will find that the sheet name is Monthly Orders Reports and the top left corner of the pivot is $A$30.
Don't get too hung up on that in the sense that almost everyone starts with letting the hard coded version of the formula simply by typing "=" and the cell in the pivot they are trying to retrieve. So you will already have populated these references as are relevant to your specific pivot table.
Process example at the end.

2) Latest Date
Its generally bad practice to rely on "Today" to drive your reports.
If you do that then how do you test it when your data isn't today's or yesterdays ? How do you look at a different days information ?
My date choice is not that important, you just need a cell to use that has the date you need to drive your 3 reports.
In my case the cell is B2.  It just happens that I wanted to show your yesterday concept in a different cell to my input cell (B1)

3) 1 Pivot or 3 Pivots
I would go with 3 pivots which apparently you have already done. If you try it with 1 the days pivot will get huge.

4) Yesterday question - I don't really understand what you are saying here once you have digested all this let me know if its still an issue.

Like I said, almost everyone starts with the automatically generated getpivot and then modifies it to suit.
Below I outline the steps that I followed:-
Note: The first identifies the pivot and the measure. The next lines are pairs representing Dimension, Criteria pairs.


----------



## ExcelAtEverything (Apr 5, 2021)

Alex Blakenburg said:


> You are going to tell the system what period you want somewhere.
> If you don't apply a filter for a years pivot then you would get a manageable number of columns.
> If you don't apply a filter to a years AND months pivot you will get number of years * no of months
> At the days (yesterday) level your are going to get no of years * 365 which really becomes unwieldy.
> ...


And sorry, but while I understand what you're saying regaring the results of each level of date filtering here, I'm having trouble understanding what you're ultimately saying to do or not to do.  Are you saying to have it at the Days level, all collapsed, just accessing it thru the GETPIVOTDATA formulas?


----------



## ExcelAtEverything (Apr 5, 2021)

Alex Blakenburg said:


> 2) Latest Date
> Its generally bad practice to rely on "Today" to drive your reports.
> If you do that then how do you test it when your data isn't today's or yesterdays ? How do you look at a different days information ?
> My date choice is not that important, you just need a cell to use that has the date you need to drive your 3 reports.
> In my case the cell is B2. It just happens that I wanted to show your yesterday concept in a different cell to my input cell (B1)


Ok that makes sense about it being bad practice, that's great advice. But not sure I understand how to create an alternative. Is your B1/B2 example an alternative? If it is, then could you explain further what is happening in B1 because =Today() is the only way I know to do that.


----------



## Alex Blakenburg (Apr 5, 2021)

I would go with a manually entered date.


----------



## ExcelAtEverything (Apr 5, 2021)

Makes sense. I did that for ease of use without really thinking about it too much, but you're right manual entry definitely provides far better control.


----------



## ExcelAtEverything (Apr 5, 2021)

Alex Blakenburg said:


> 1) A GetPivot function needs an anchor to tell it which pivot table it is using, this is generally the top left corner of the pivot table.
> If you have a look at the first spreadsheet image I have above you will find that the sheet name is Monthly Orders Reports and the top left corner of the pivot is $A$30.
> Don't get too hung up on that in the sense that almost everyone starts with letting the hard coded version of the formula simply by typing "=" and the cell in the pivot they are trying to retrieve. So you will already have populated these references as are relevant to your specific pivot table.
> Process example at the end.


Lol! 10-4. I'm pretty tired and I was just looking at it thru the lens of my own sheet, and that cell reference wasn't in the table so it confused me. Combination of my newb-ness and sleep deprivation. Thx!?


----------



## Alex Blakenburg (Apr 5, 2021)

I am in Sydney Australia and it’s just gone past midnight, so I will be calling it a night too.


----------



## ExcelAtEverything (Apr 5, 2021)

Ha! Well goodnight Alex. You have been a tremendous help, thank you. I'm gonna push thru and stay up until tonight. Im in Wash DC.


----------



## ExcelAtEverything (Apr 4, 2021)

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 (Apr 5, 2021)

Alex Blakenburg said:


> 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 (Apr 5, 2021)

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 (Apr 5, 2021)

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 (Apr 5, 2021)

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 (Apr 5, 2021)

ExcelAtEverything said:


> 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 (Apr 5, 2021)

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 (Apr 5, 2021)

Alex Blakenburg said:


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


Great pointers, and I will. Thx


----------



## ExcelAtEverything (Apr 5, 2021)

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 (Apr 5, 2021)

ExcelAtEverything said:


> 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).
> 
> ...



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 (Apr 6, 2021)

Alex Blakenburg said:


> =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!???


----------



## ExcelAtEverything (Apr 4, 2021)

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 (Apr 6, 2021)

ExcelAtEverything said:


> 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 (Apr 6, 2021)

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 (Apr 6, 2021)

ExcelAtEverything said:


> 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 (Apr 6, 2021)

Isn't after 2am there ?


----------



## ExcelAtEverything (Apr 6, 2021)

Alex Blakenburg said:


> 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 (Apr 6, 2021)

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 (Apr 6, 2021)

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 (Apr 6, 2021)

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 (Apr 6, 2021)

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


----------



## Alex Blakenburg (Apr 6, 2021)

ExcelAtEverything said:


> 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!!! ?  ?  ?
> 
> 
> 
> ...



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.


----------



## ExcelAtEverything (Apr 4, 2021)

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 (Apr 6, 2021)

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


----------



## ExcelAtEverything (Apr 6, 2021)

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 (Apr 6, 2021)

ExcelAtEverything said:


> 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 (Apr 7, 2021)

Gotcha. Many thanks.


----------



## ExcelAtEverything (Apr 7, 2021)

Alex Blakenburg said:


> 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 (Apr 9, 2021)

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 (Apr 10, 2021)

ExcelAtEverything said:


> 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 (Apr 10, 2021)

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.


----------

