Dynamically filter row based on dates and ID

[carlosyanez]

Hi,

I'm currently working on a Dashboard where I need to pick the latest value for one category, with the ability to use a time slicer and then get the latest value within the range of the filter.  The case is as follows::

ID1	Date	Value
A	01-Jan-22	4
A	03-Mar-22	5
A	04-Apr-22	3
B	02-Feb-22	7
C	04-Mar-22	9
C	05-Jul-22	7

Let's say I have the above table. What I need is to just present the latest row for each ID, i.e.:

ID1	Date	Value
A	04-Apr-22	4
B	02-Feb-22	7
C	05-Jul-22	7

But I had a date slicer, with an end date on 5 May, the result would be:

ID1	Date	Value
A	04-Apr-22	4
B	02-Feb-22	7
C	04-Mar-22	9

And if the end of the range is set to 3 March, then it needs to present:

ID1	Date	Value
A	03-Mar-22	5
B	02-Feb-22	7

What would be the best way to achieve this with DAX measures/calculated columns or tables?

Thanks!

Carlos

[FreemanZ · Accepted solution]

hi @carlosyanez 

try to created two measures and plot a visual table with them. 

the code:

DateLatest := MAX(TableName[Date])

ValueLatest :=

VAR DateLatest = MAX(TableName[Date])

RETURN

CALCULATETABLE(

    VALUES(TableName[Value]),

    TableName[Date] = DateLatest

)

I tried and it worked like this: