DAX Measure to reference Pivot Table Column. Return most recent row of table

Question

I'm using Excel 265.

I have a list of stock transactions imported via Power Query using an SQL-Server connection loaded to the data model.

I want to create a Pivot table called 'PivotTable1' to return a unique list of 'ITEMID's.

Then have the second column in the Pivot return the most recent 'UNIT_PRICE' per 'ITEM_ID' in the Pivot.

The idea is I can then slice the pivot to a date in the past and the Pivot will return the most recent price for each item up to that date.

INPUT / RAW TABLE

OUTPUT / POWERPIVOT

Additional context I think the slightly more efficient way to handle this is to prefilter the data in SQL to only give me the most recent dates per item based on a date the user provides. I can then handle this request using a cell and a button to fire off some VBA. Then when the pivot populates it's only pulling one row of data per date, per location. However it would still be nice to know if I could force this to work in DAX.

Answer 1

This is the measure you need to create in DAX:

Latest_Price = 
VAR SummaryTable = ADDCOLUMNS(
    SUMMARIZE(INPUT_TABLE,INPUT_TABLE[ITEM_ID],INPUT_TABLE[UNIT_PRICE]),
    "MaxDate",CALCULATE(MAX(INPUT_TABLE[DATE]))
)
RETURN
    MAXX(
        SummaryTable,[UNIT_PRICE])

It is a good DWH practice to create a calendar table to use as a date filter to pick year from :

This is a code to create a calendar table in DAX using Create table statement:

CalendarTable = 
    ADDCOLUMNS(
        CALENDAR(
        DATE(2002,01,01),DATE(2023,12,31)
    ),
    "Year",YEAR([Date]),
    "Month",Month([Date]),
    "Day",Day([Date])
    )

Your Calendar table looks like:

and relationship in data Model:

Then you can create a table visual and put the fields in suitable places.

Put Year field from calendar table on the slicer, and move the slider to see the change in price column, as I did! It is dynamic!