Most recent referenced value

[KRejko]

Hello,

I am trying to get the most recent value referenced from my current record. Currently, I am using the formula:

ANY(
     TOP(
          SELECT(
               [Related Invoices][Status], 
               [Due Date]=MAX([Related Invoices][Due Date]), 
          ),
          1
     )
) 

I have used a nearly identical formula elsewhere in the same app and this approach has worked successfully. However, in this case, the formula returns an empty value for the [Related Invoices][Status].

To try and debug this I have pulled out the ‘MAX’ part of the formula into its own virtual column and can validate that this is picking up the most recent value.

Also, this value returned from the max function does match the most recent invoice date in the invoice table directly above:
—image redacted due to new user limitations—

Thinking maybe this was a formatting error, I have also tried wrapping these dates in a DATE() function, but this did not solve the issue.

  DATE([Due Date])=DATE(MAX([Related Invoices][Due Date]))

I have also seen others use the MAXROW() function to achieve a similar outcome. However, I am not sure that this works in my case due to needing records related to the current invoice.

Any help or suggestions on how to fix or debug this issue would be greatly appreciated. Thanks in advance!