Calculate max value in date range and display only top result

[ToPa]

Hi!

I have tried to search for similar cases, but can not seem to find the correct solution for creating this measure.

I have two tables - Date Dim and Fact Product. Dim date has date values and Fact Product as below (example data)

(The Value-column is a calculated column and I can not filter Top N by this value in the visual.)

ID	Product	Color	Value	Value Date
1	ABC123	Red	300	1/30/24
2	ABC123	Red	425	1/30/24
3	ABC123	Red	150	1/30/24
4	DEF456	Blue	225	2/2/24
5	DEF456	Blue	150	2/2/24
6	ABC123	Red	200	2/3/24
7	ABC123	Red	123	1/28/24
8	ABC123	Red	500	1/31/24

When selecting a date range from Dim Date in a slicer, I would like to calculate the values for the rows with same product name and color and find which Value Date this was found. I do not want to see all products that date, or all colors - only the one that meet the requirments.

Expected result from example 

Selected date range 12/11/23 - 2/3/24 in slicer, visual should display:

Product	Color	Value	Value Date
ABC123	Red	875	1/30/24

If selected date range is 2/2/24 - 2/3-24, visual should display:

Product	Color	Value	Value Date
DEF456	Blue	375	2/2/24

Any idea on how this can be achieved? 🙂

[talespin · Accepted solution]

hi @ToPa 

You can use RANK, I have used Date from Fact table, need to check how to use date from Date table in Rank. Apply visual filter on measure rank = 1.

Rank =

VAR _Rank =

RANK(

        DENSE,

        ALLSELECTED(TestTbl5[Product], TestTbl5[Color], TestTbl5[Value Date]),

        ORDERBY( [Sum Value], DESC)

)

RETURN _Rank