[SOLVED] Showing the value corresponding to the biggest date per date group in Pivot Table in Excel

Showing the value corresponding to the biggest date per date group in Pivot Table in Excel

Edit: This is my first question so I can't yet embed images.

Consider a very simple dataset that has a Date column and a Value column. The Value column can be a numeric measurement of any kind.

Screenshot of a simple Excel table with a Date column and a Value column

I want to create a Pivot Chart out of this dataset to visualize and interact with the data. However, when grouping the Date field in the corresponding Pivot Table, the Value field can only be summarized as a sum, count, average, min, max, etc., and I want the Pivot Table to show the Value corresponding to the biggest date in the Date group.

For example, if I don't group at all the Pivot Table correctly shows the Value for each day. But if I group Date by months I want the Pivot Table to show the Value corresponding to the last date of the month not the sum of Values for the entire month, the count of Values, the average of Values, etc. Similarly, if I group by quarter I want to get the Value corresponding to the last day of the quarter; if I group by year, then the Value for the last day of the year and so on.

Ι've already tried looking into Calculated Fields but I can't use XLOOKUP. I've also tried rearranging the fields in the Pivot Table as suggested by this other question Pivot tables in excel - showing last values, but that results in the latest Date for each Value (e.g. the latest date where the value "42" was seen).

The most promising thing I've tried is using the Date field in the Rows and Values section of the Pivot Table Fields panel and then adjusting the Value Field Settings to summarize by max.

Screenshot of the Pivot Table Fields panel showing the Date field in the Rows and Values sections

This returned the biggest date per Date group which seems closer to what I want but I don't know yet how to get the corresponding Value for that date (within the Pivot Table that is).

Screenshot of the resulting Pivot Table based on the previous settings

Solution

With the following measure defined within Power Pivot:

Value for Latest Date :=
VAR LatestDate =
    MAX( Table1[Date] )
RETURN
    CALCULATE(
        MAX( Table1[Value] ),
        Table1[Date] = LatestDate
    )

which can then be dragged into the Values area of the Pivot Table.