Forum Discussion
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 consists of different countries and items. I have tried using the lastdate, max functions but I cant seem to work out the logic.
Thanks for your help!
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
5 Replies
Brighton10 , Try a measure like
lastnonblankvalue(Table[datetime], max(Table[Status]))
calculate(lastnonblankvalue(Table[datetime], max(Table[Status])), all(Table))
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
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?
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
Try the below Measure by considering Datetime Column has a proper datetime format.
Measure = CALCULATE(SUM(Sheet10[Status]),FILTER(Sheet10,Sheet10[Date]=MAX(Sheet10[Date])))
