PIVOT TABLE EXTRACTION

Scuba94

New Member
Joined
Oct 11, 2023
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have the below pivot tabel created and when using GETPIVOTDATA to extract a given month, i get the below formula:

=GETPIVOTDATA("CPU ",'CPU KPi'!$AC$4,"Month Name","Sep","Year","2023")

My issue is I need to change/swap out the month, in this example 'Sep' for a named cell that has months in it, bassically I have a slicer with a list of months from Jan to Dec and users selects the given month and I then pull the data from the pivot for the selected month and but also the previous month so i can do a comparison.

In the past when I've used GETPIVOTDATA my formula looks like the below:

=IFERROR(GETPIVOTDATA("[Measures].[Average of CPU in GBP]",'CPU Pivot'!$J$4,"[BespokeRawData].[Delivered Year]","[BespokeRawData].[Delivered Year].&[2023]","[BespokeRawData].[Delivered Month]","[BespokeRawData].[Delivered Month].&[Sep]"),"")

And i just change the 'Sep' but to read '&["&Previous_Month&"]")' and it all works fine, however I an unabl to so this for the first formula shown at the top of this post.

Can anyone point me in the right direction here, would be much appreaciated.

Thank you.

Sum of CPUColumn Labels
Row LabelsAirOceanSea-AirTruckGrand Total
2022
Jan
£0.344​
£0.106​
£0.016​
£0.165​
Feb
£0.272​
£0.068​
£0.015​
£0.090​
Mar
£0.317​
£0.060​
£0.017​
£0.119​
Apr
£0.364​
£0.093​
£0.024​
£0.146​
May
£0.435​
£0.072​
£1.339​
£0.025​
£0.142​
Jun
£0.213​
£0.181​
£0.027​
£0.165​
Jul
£0.249​
£0.107​
£0.042​
£0.193​
Aug
£0.214​
£0.149​
£0.026​
£0.139​
Sep
£0.201​
£0.127​
£0.024​
£0.144​
Oct
£0.222​
£0.122​
£0.031​
£0.111​
Nov
£0.193​
£0.155​
£0.020​
£0.108​
Dec
£0.134​
£0.104​
£0.028​
£0.094​
2022 Total
£0.241​
£0.105​
£1.339​
£0.024​
£0.132​
2023
Jan
£0.238​
£0.112​
£0.029​
£0.094​
Feb
£0.295​
£0.077​
£0.025​
£0.076​
Mar
£0.189​
£0.078​
£0.030​
£0.072​
Apr
£0.188​
£0.094​
£0.029​
£0.075​
May
£0.111​
£0.078​
£0.022​
£0.062​
Jun
£0.194​
£0.062​
£0.031​
£0.063​
Jul
£0.266​
£0.066​
£0.032​
£0.071​
Aug
£0.165​
£0.044​
£0.032​
£0.055​
Sep
£0.269​
£0.041​
£0.035​
£0.041​
Oct
£0.189​
£0.042​
£0.030​
£0.048​
Nov
£0.168​
£0.034​
£0.030​
£0.040​
2023 Total
£0.201​
£0.070​
£0.029​
£0.066​
Grand Total
£0.233​
£0.080​
£1.339​
£0.027​
£0.097​


=GETPIVOTDATA("CPU ",'CPU KPi'!$AC$4,"Month Name","Sep","Year","2023")
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Ignore,

I've worked it out, simple really, just change the month name to the name of the named cell i.e.

=GETPIVOTDATA("CPU ",'CPU KPi'!$AC$4,"Month Name","Sep","Year","2023")

becomes

=GETPIVOTDATA("CPU ",'CPU KPi'!$AC$4,"Month Name",Previous_Month,"Year","2023")

My names cell is linked to a slicer with the month names so the lookup becomes dynamic
 
Upvote 0
Solution

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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