LOOKUPVALUE with IF

[kimberlyrose1x]

Thank you in advance for your help.

I have two tables:

Arrest Table includes date of arrest, name number, and current street address

NMHistory Table includes name number, street address, Begin Date and Expiration Date for that address

Currently, the Arrest Table updates with the current address and not the address the person had when they were arrested.

I would like to say something to the effect of:

If the name number in Arrest Table matches the name number in NMHistory table, look at date of arrest and find where the date fits within the Begin Date and Expiration Date. If the date fits, return the street address. If the arrest date is greater than the greatest expiration date in the NMHistory Table, return the street address from the Arrest Table.

Here is a visual with sample data:

I'm totally stumped. Thank you so much for helping.

Crystal

[tamerj1 · Accepted solution]

Hi @kimberlyrose1x 
You can create a new calculated column in the Arrest table

Steet at Arrest =
MAXX (
    FILTER (
        NMHistory,
        NMHistory[Number] = Arrest[NameNum]
            && NMHistory[Begin Date] <= Arrest[Date]
            && NMHistory[Exp Date] >= Arrest[Date]
    ),
    NMHistory[Street]
)

[kimberlyrose1x]

Thank you so much!!! You saved the day again. 🙂