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]

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]

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]

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]

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]

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]

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]

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]

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]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
29
30	Sum of Line Item total	Column Labels
31		2020		2020 Total	2021							2021 Total	Grand Total
32		Apr	Apr Total		Feb	Feb Total	Mar	Mar Total	Apr		Apr Total
33	Row Labels	4-Apr			4-Feb		4-Mar		1-Apr	4-Apr
34	237	1,020	1,020	1,020	1,000	1,000	10,003	10,003	10,041	10,044	20,085	31,088	32,108
35	839	2,020	2,020	2,020	2,000	2,000	20,003	20,003	20,041	20,044	40,085	62,088	64,108
36	1193	3,020	3,020	3,020	3,000	3,000	30,003	30,003	30,041	30,044	60,085	93,088	96,108
37	1246	12,020	12,020	12,020	4,000	4,000	40,003	40,003	40,041	40,044	80,085	124,088	136,108
38	Grand Total	18,080	18,080	18,080	10,000	10,000	100,012	100,012	100,164	100,176	200,340	310,352	328,432
39
Monthly 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.xlsx
	A	B	C	D	E	F
1	Latest Date	5/04/2021
2	Yesterday	4/04/2021
3
4
5	Yestderday
6
7	Org #	Loc Name	GS$ Yesterday	MTD	YTD
8	237		10,044	20,085	31,088
9	839		20,044	40,085	62,088
10	1193		30,044	60,085	93,088
11	1246		40,044	80,085	124,088
12
Table with getpivot

Cell Formulas
Range	Formula
B2	B2	=B1-1
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))
D8:D11	D8	=GETPIVOTDATA("Line Item total",'Monthly Orders Reports'!$A$30,"Org #",[@[Org '#]],"Months",MONTH($B$2),"Years",YEAR($B$2))
E8:E11	E8	=GETPIVOTDATA("Line Item total",'Monthly Orders Reports'!$A$30,"Org #",[@[Org '#]],"Years",YEAR($B$2))