Forum Discussion

Brighton10's avatar
Brighton10
Helper II
5 years ago
Solved

Return value based on latest date

Hello,   I have a table that has the following columns (attached) and i would like to calculate/return only the latest status, in this example 37. This is just a subset of the entire dataset which ...
Need Help
  • Pragati11's avatar
    Pragati11
    5 years ago

    Hi Brighton10 ,

     

    You can use a dax as follows:

    StatusInLast24Hours = 
    var t1 = SELECTEDVALUES(tablename[datetime])

    var t2 = t1 - 1

    RETURN

    CALCULATE(MAX(tablename[status]), tablename[dateTime] <= t1 && tablename[dateTime] > t2)

     

    Thanks,

    Pragati

     

Pragati11's avatar
Pragati11
Super User
5 years ago

Hi Brighton10 ,

 

You can write the following DAX measure:

RecentStatus = 
var t1 = MAX(tablename[dateTime])

RETURN

CALCULATE(MAX(tablename[status]), tablename[dateTime] = t1)

 

thanks,

Pragati

  • Brighton10's avatar
    Brighton10
    Helper II
    5 years ago

    Thanks Pragati11 , Your solution works perfectly. Just a follow up question. If i want the datetime to filter from the previous 24hrs. How can i implement that?

    • Pragati11's avatar
      Pragati11
      Super User
      5 years ago

      Hi Brighton10 ,

       

      You can use a dax as follows:

      StatusInLast24Hours = 
      var t1 = SELECTEDVALUES(tablename[datetime])

      var t2 = t1 - 1

      RETURN

      CALCULATE(MAX(tablename[status]), tablename[dateTime] <= t1 && tablename[dateTime] > t2)

       

      Thanks,

      Pragati