Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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.
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
@Brighton10 , Try a measure like
lastnonblankvalue(Table[datetime], max(Table[Status]))
calculate(lastnonblankvalue(Table[datetime], max(Table[Status])), all(Table))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |