Show latest value based on date and other criteria (measure or new column)

[schaezac]

Hi,

I am trying to create a measure or column that will allow me to easily bring the latest value into my visuals. Here is some example data; the highlighted column is what I'm trying to create.

The end goal is to show the most recent sale as a flat line across a graph, so I can compare historical sales to the most recent one visually, by product. Also, the date needs to be dynamic so it automatically updates the values when fresh data is loaded.

Thanks!

[techies · Accepted solution]

Hi @schaezac please check this

MostRecentSalePerProduct =

VAR LatestDateForProduct =

    CALCULATE(

        MAX('Sheet2'[Date]),

        ALLEXCEPT('Sheet2', 'Sheet2'[Product])

    )

RETURN

CALCULATE(

    MAX('Sheet2'[Sale]),

    ALLEXCEPT('Sheet2', 'Sheet2'[Product]),

    'Sheet2'[Date] = LatestDateForProduct

)

[schaezac]

EDIT: please disregard follow up - fixed my date table range to correct this. Thanks again, you're a lifesaver!

This worked - thanks so much! One last question - is there a way to curtail the data so it does not extend past my most recent data? For example, in the graph below, my most recent data is in January 2025. I would like the dotted line (the one you helped me create) to stop in January as well. I think this is due to my date table extending far beyond the latest actual data.