cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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!

1 ACCEPTED SOLUTION
Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

5 REPLIES 5
Super User

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])))

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Helper II

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?

Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Super User

@Brighton10 , Try a measure like

lastnonblankvalue(Table[datetime], max(Table[Status]))

calculate(lastnonblankvalue(Table[datetime], max(Table[Status])), all(Table))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.