Forum Discussion

carlosyanez's avatar
carlosyanez
Frequent Visitor
3 years ago
Solved

Dynamically filter row based on dates and ID

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::

 

ID1DateValue
A01-Jan-224
A03-Mar-225
A04-Apr-223
B02-Feb-227
C04-Mar-229
C05-Jul-227

 

 

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

 

ID1DateValue
A04-Apr-224
B02-Feb-227
C05-Jul-227

 

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

 

ID1DateValue
A04-Apr-224
B02-Feb-227
C04-Mar-229

 

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

 

ID1DateValue
A03-Mar-225
B02-Feb-227

 

 

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

 

 

Thanks!

Carlos

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

     

     

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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: