Expression to return last item by date

SSAS and SSRS
1

Sample Table:
Date Action Status
2/24/23 Inspection Pass
2/25/23 Inspection Pass
2/26/23 Inspection Fail

I'm trying to write an expression that will provide the status that has the latest date, in this case that would be Fail.

I tried: =Last(Fields!Status.Value, "DataSet1")

Would I need a MaxDate in there somewhere? Thank you.

2

hi

this is a microsoft t-sql forum

SELECT 
    Max(Date)
FROM
   SampleTable

all depends on how your data is

3

Oops, thank you.

4

Yes, you would need to incorporate the maximum date value into your expression to get the latest status. Here's one way you can do it:

=Lookup(Max(Fields!Date.Value, "DataSet1"), Fields!Date.Value, Fields!Status.Value, "DataSet1")

This expression uses the Lookup function to find the status corresponding to the maximum date value in the "Date" field of the "DataSet1". The Max function is used to get the maximum date value in the dataset.

Note: Make sure to replace "DataSet1" with the actual name of your dataset in your report.

6

These expressions got me there:

=Lookup(Fields!ProjectNumber.Value, Fields!ProjectNumber.Value, Fields!MaxFailDate.Value,"MaxFailDate")

=IIF(ReportItems!MaxPassDate.Value > ReportItems!Due.Value, "Pass", IIF(ReportItems!MaxFailDate.Value > ReportItems!MaxPassDate.Value ,"Fail", "Due"))