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.
=GETPIVOTDATA("CPU ",'CPU KPi'!$AC$4,"Month Name","Sep","Year","2023")
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 CPU | Column Labels | ||||
| Row Labels | Air | Ocean | Sea-Air | Truck | Grand 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")
