How to determine earliest/latest value with multiple criteria?

[GooseHelpful199]

I receive monthly reports that has a week# and Item#, which I combined together using Power Query. However, I am looking to label the earliest week and latest week from each Item+Data Import Date combination, using a formula that would function like below in DAX. Any suggestions on how I can achieve this?

[amitchandak · Accepted solution]

@GooseHelpful199 , a new column

new column =

var _max = maxx(filter(Table, Table[Item] = earlier(Table[Item]) ) , Table[Week])

var _min = minx(filter(Table, Table[Item] = earlier(Table[Item]) ) , Table[Week])

return

Switch( True() ,

[Week] = _max = "Last",

[Week] = _min = "First",

blank())

For measure refer

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[TwinJohnson]

@amitchandak 

Hello Amitch,

I was wondering if you could assist in my dilema below.  It's sSimilar to the question you assisted with above in which I'm trying to determine the earliest value with/without filters.  I have an order file and date table file but ended up putting the week ending date (based on the order date) in the order file.  For each week ending I'm trying to determine the oldest order still open in the entire data set.  An order is still open on that week ending,  if on each recorded week ending date the ship date is not blank or the ship date is after the week ending.  I've created a measure that identifies the earliest date per week ending then used the measure in a calculated column to determine the number of days between the two but cannot seem to get either the measure or calculated column correct.  I really appreicate it!  Your a wizard on this forum.

Kind regards,

Logic behind calculation