Dynamically filter row based on dates and ID

carlosyanez — Mon, 28 Nov 2022 09:22:41 GMT

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

Re: Dynamically filter row based on dates and ID

FreemanZ — Mon, 28 Nov 2022 16:09:10 GMT

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:

topic Dynamically filter row based on dates and ID in DAX Commands and Tips

Dynamically filter row based on dates and ID

Re: Dynamically filter row based on dates and ID