I have a table of payments and a table of cases. although there exists a one to many relationship, RELATED does not seem to be working.
There are multiple payments for the same case with different payment dates.
what I am trying to achieve is to to extract the first payment date for any case. for this I used the following formula:
Payment Date = LOOKUPVALUE(Paid[Payment Date],Paid[Case-WE Month],Case[Case-WE Month], min(Paid[Payment Date]))
I am receiving the following results. as you can see that it is giving me dates which precede the date of the data itself i.e. the dataset starts from October 2022, whilst I am getting the invalid date of 2 October 2020. as a result, I am getting negative payment days as well.