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]

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]

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]

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!

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]

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)

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]

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]

I would go with a manually entered date.

 

[ExcelAtEverything]

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]

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]

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

 

[ExcelAtEverything]

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.