Return Date based on Measure which calculates the Lowest Item Cost

[Asantos2020]

Hi there,

I currently have a table containing:

ID, Date, Item, Unit Cost

I've created the measures below, so that I can have the lowest costs VS the highest cost plotted on a table visual.

Lowest Cost =

CALCULATE(

MIN(Table[Unit Cost]);

ALLEXCEPT(Table;Item))

Highest Cost =

CALCULATE(

MAX(Table[Unit Cost]);

ALLEXCEPT(Table;Item))

When the user clicks on a product, he should see:

The lowest cost was on <DATE> and the highest one was on <DATE> and these dates are what I'm trying to get.

It's important to say that there is a date slicer and these extracted dates should escape such filters.

Thank a lot!

[d_gosbell · Accepted solution]

It's hard to answer this without knowing how your data model is structured, if you just have a single table something like the following might work. Note that it could be possible that there are multiple dates for a given Lowest Cost, so I'm using MIN to get the earliest one (you could switch this to use MAX if you want the most recent date)

Low Date=CALCULATE( MIN(Table[Date]) ; FILTER(Table ; Table[UnitCost] = [Lowest Cost] ) ; ALL(Table[Date]) )

You should be able to easily copy this pattern and create a version for the Highest Cost.

[Asantos2020]

Hello @d_gosbell !

This is exactly what I needed. The data is comprised of one table only...the one we're working on.

I've already applied this one to the highest cost and to other extractions as well. Just needed to grasp the principle - syntax wise.

Cheers,

Antonio Santos