Lookupvalue and/or firstnonblank help to get most recent value

[MarkShoo]

Hi,

I'm trying to get the most recent transaction date for a specifc letter name (Recovery Insurer 3 (sending losses)) for each individual incident no.

There are sometimes multiple records of the same letter being sent on different days. The data is sorted by the most recent day coming first.

How do I use the lookupvalue and firstnonblank formulas to solve the issue? I've seen other related articles, but can't seem to solve it... 

The data sets are:

Main table to try and get latest letter date added in:

Table 'Weekly Report'

Table that has the multiple letters:

Table 'Recovery Letters Test'

So I would want table 1 to return 24/05/2021 for incident no 123456 for the 'Recovery Insurer 3 (sending losses)' letter as that is the latest date that 'Recovery Insurer 3 (sending losses)' letter has been completed. 

Thanks

[richbenmintz]

Hi @MarkShoo,

Please try the following calc column formula

Latest Date = 
var _incident = 'Weekly Report'[Incident No]
return
CALCULATE(max('Recovery Letters Test'[Trans'n Dt]), FILTER('Recovery Letters Test', 'Recovery Letters Test'[Incident No] = _incident))

Thanks,

[MarkShoo]

Thanks. 

That gives the latest date of any letter sent, but can you filter it to specific letter names? IE for the 'Recovery Insurer 3 (sending losses)' letter?

[richbenmintz · Accepted solution]

Hi @MarkShoo,

you would just need to include that in your filter, see below

Latest Date = 
var _incident = 'Weekly Report'[Incident No]
return
CALCULATE(max('Recovery Letters Test'[Trans'n Dt]), FILTER('Recovery Letters Test', 'Recovery Letters Test'[Incident No] = _incident && 'Recovery Letters Test'[Description] = "Recovery Insurer3 (sending losses)"))

 Thanks

[MarkShoo]

That's worked perfectly - Thanks @richbenmintz much appreciated