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")