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.

score 1 · Accepted Answer · answered Aug 25 '23 at 17:43

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!

answered Aug 25 '23 at 17:43

Ozan Sen

2,477
2
4
16

Thankyou, one question. Where do I put this? I don't see an advanced editor equivalent for DAX in Excel 365. – Michael.C Aug 27 '23 at 22:11
You're welcome, @Michael.C: This is Power BI. If you want to do it in Excel, You need to download Power Pivot add-in: You can find in the link how to bring Power Pivot tab into your excel screen, and start creating measures using DAX codes: https://support.microsoft.com/en-us/office/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8 – Ozan Sen Aug 27 '23 at 22:29
Hi Ozan, I figured it out. You can't create these types of formulas as measures in Power Pivot's field editor nor can you do it in the manage data model area. You need to import the table into the data model and then download it back into a sheet, then right click the table and navigate to Table > Edit DAX. – Michael.C Aug 27 '23 at 23:15
Exactly, Micheal! You're so smart. I personally find Power BI more tidy and structured, so It would be better for you to switch to Power BI Desktop, which is a free tool to download. Mentality is almost the same(Import your tables, create your relationships between tables - build your data model, then create your metrics - measures, calculated columns etc.) https://powerbi.microsoft.com/en-us/desktop/ – Ozan Sen Aug 28 '23 at 05:16

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

1 Answers1