How to: Show the date when the max value happened?

LRATOZ

Board Regular
Joined
Aug 17, 2014
Messages
59
Office Version
  1. 2016
Platform
  1. Windows
Hi,
To keep things simple:
Column A (Date)has the dates
Column B (Value) has the values

I want to find the MAX-value AND the date it happened in pivot tables
Now when I run pivot tables it shows me the year and the quarters.
I don't need the Quarters, so when I deselect this then it shows me the months and the maximum value for that month.
This is a step in the right direction.
I now see the following:
Year, month, max value
My problem is that I would like to see the following:
Year, month, max value and the date it occurred. (Similar to the INDEX function).

Can somebody steer me in the right direction please?
Many thanks in advance!
By the way: I am new to pivot tables, please be gentle:)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
OK, to make it easier I have copied part of my pivot table:
Pivot table 1.jpg
Pivot table 2.jpg

As you can see I added a column outside the pivot table and used the INDEX function to show the date when the max temperature happened.
Can this be done inside the pivot table itself?
I have tried to insert the 4th column between the 2nd and the 3rd column but the pivot table doesn't allow that to happen.
Hopefully this might be a bit clearer compared to the previous message.
Cheers,
Luke
 
Upvote 0

Forum statistics

Threads
1,214,432
Messages
6,119,468
Members
448,900
Latest member
Fairooza

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