Displaying most recent value available, even when other columns have a MORE recent value available

[Margaret]

Hello,

I need to display the most recent value available, along with the date it is associated with, even when other values are associated with an even more recent date. (Then, second step: compare that value with another value associated with that date, to determine what color to display the first value in.) Attached is a sample qvw. Also below is a smaller set of sample data.

I am able to successfully do what is needed for the first two columns, using 

=FirstSortedValue([OP MARGIN BCH],-DateKey)

since there are no later dates than the dates for the values I want.

But I want a chart to display for

Monthly Mortality, like this:

8/1/2022           0.1 (in red)             0.85

and for New Patient Acquisition, like this:

9/1/2022          82988 (in red)            85000

sample data (also in attached qvw)

DateKey	OP MARGIN BCH	OP MARGIN S&P	MONTHLY MORTALITY RATE BCH	MORTALITY RATE - TARGET	NEW PATIENT ACQUISITION	NEW PATIENT ACQUISITION - TARGET
7/30/2022	-	-	-	-	-	-
7/31/2022	-	-	-	-	-	-
8/1/2022	-	-	0.1	0.85	82,809	84000
8/2/2022	-	-	-	-	-	-
8/29/2022	-	-	-	-	-	-
8/30/2022	-	-	-	-	-	-
8/31/2022	-	-	-	-	-	-
9/1/2022	-	-	-	-	82988	85000
9/2/2022	-	-	-	-	-	-
9/30/2022	-	-	-	-	-	-
10/1/2022	-	-	-	-	-	86000
10/2/2022	-	-	-	-	-	-
10/3/2022	-	-	-	-	-	-
10/4/2022	-	-	-	-	-	-
10/31/2022	-	-	-	-	-	-
11/1/2022	-	-	-	-	-	87000
11/2/2022	-	-	-	-	-	-
11/27/2022	-	-	-	-	-	-
11/28/2022	-	-	-	-	-	-
11/29/2022	-	-	-	-	-	-
11/30/2022	-	-	-	-	-	-
12/1/2022	0.2	0.019	-	-	-	88000