GetPivotData Relative Formula Assistance

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
351
Office Version
  1. 2019
Platform
  1. Windows
Hello,
In the formula below, the 2nd line ([Date]) is causing me to receive a #REF error. In the part immediately after TEXT, F$5 refers to a date formatted cell showing the date "Monday, March 21, 2021", and the reason I'm subtracting -365 from it is because I want to reference the info from the same day last year.
=GETPIVOTDATA("[__Xl2].[Measures].[Sum of Line Item Total 3]",'LY GS & GP Daily-MTD'!$B$3,
"[LY Orders Report 2].[Date]","[LY Orders Report 2].[Date].&["&TEXT(F$5-365,"yyyy-mm-dd")&"01T00:00:00]",
"[LY Orders Report 2].[Org #]","[LY Orders Report 2].[Org #].&["&$C8&"]")


Starting with the static version of the cell reference below, I removed lines 3 ([Month]) and 4 ([Year]) because I don't need them. To be sure, I tried adding them back in, but didn't make any difference.
=GETPIVOTDATA("[Measures].[Sum of Line Profit 2]",'LY GS & GP Daily-MTD'!$B$3,
"[LY Orders Report 2].[Date]","[LY Orders Report 2].[Date].&[2020-06-01T00:00:00]",
"[LY Orders Report 2].[Date (Month)]","[LY Orders Report 2].[Date (Month)].&[Jun]",
"[LY Orders Report 2].[Date (Year)]","[LY Orders Report 2].[Date (Year)].&[2020]",
"[LY Orders Report 2].[Org #]","[LY Orders Report 2].[Org #].&[237]")

Thanks.
1622803740914.png
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hopefully we get lucky and its just this.
Your date formula is giving this result.
2020-05-3101T00:00:00
You need this
2020-05-31T00:00:00
So start with changing the
01T00:00:00 in the formula to T00:00:00

Assuming that works, I would then recommend changing
F$5-365
to
EDATE(F$5,-12)
the 12 is minus 12 months. This will still work across leap years while -365 will not.
If you want to test that out and using 31/05/2021 try going back 2 years
F$5-365*2 in current formula vs EDATE(F$5,-24)
 
Upvote 0
Solution
Hi Alex! Thank you so much! Worked perfectly, and I also took your advice on EDATE. I hadn't thought about that! My eyes blew right past that "01".
 
Upvote 0
Alex, using EDATE sends it back to the same DATE last year, but I need the same DAY last year. I altered the formula to EDATE(F$5,-12)+1 to reflect that. Thanks again for your help!
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top