Get Minimum Date per Category based on Minimum Value per Category

[Anonymous]

Hi,

I need some help for a relative "simple" problem . I have a table with values per date and category.

Now I would like to see the minimum value per category and its RELATED minimum date.

However I'm not succeeding in adding the "minimum" date for the "minimum" value.  You should note that the minimum date is related/dependent on the "minimum" value, so I'm not interested in the minimum date per category but rather the date where the minimum value took place.

Here is an example in excel, how can I do this in DAX ?

Requested result is Category + mIn value + min date

In SQL code, I would do it like this:

SELECT t0.Category,Min(Date) as MinDate, Min(t1.MinValue) as MinValuePerCategory
FROM table t0
INNER JOIN
(
SELECT Category, Min(Value) as MinValue
FROM table
group by Category
) as t1

ON t0.Category=t1.Category
AND t0.Value=t1.MinValue

Many thanks

Tim

[v-yalanwu-msft]

Hi, @Anonymous ;

Please try to create a measure.

Measure = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Category]=MAX('Table'[Category])&&[Value]=MAX('Table'[Value])))

If you have two tables, you can change it.

Measure = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Category]=MAX('Table'[Category])&&[Value]=MAX('t1'[Value])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Both as new columns

Min Value =
minx(filter(Table, [Category] = earlier([Category])),[Value])

Min Date =
minx(filter(Table, [Category] = earlier([Category]) && [Value] = [Min Value]),[Date])