cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Asantos2020
Advocate II
Advocate II

Return Date based on Measure which calculates the Lowest Item Cost

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!
1 ACCEPTED SOLUTION
d_gosbell
Super User II
Super User II

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.

View solution in original post

2 REPLIES 2
d_gosbell
Super User II
Super User II

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.

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors