Custom Function to lookup Date from another table with filter

Hi msommerf 

Since all tables have the AssetID field, you could try below code to create a column.

Last Inspection 2 = 
MAXX (
    FILTER (
        bi_vw_InspectionCompleted,
        bi_vw_InspectionCompleted[AssetID] = bi_vw_wr_AssetIncidentReport[AssetID]
            && bi_vw_InspectionCompleted[Inspection Completed] < bi_vw_wr_AssetIncidentReport[Date_Occured]
    ),
    bi_vw_InspectionCompleted[Inspection Completed]
)

If you want to try Power Query, you could add below steps to bi_vw_AssetIncidentReport query. I'm not sure whether this would run into memory resource issues when large data is loaded, you may have a try. I attach the pbix for your reference. 

    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"AssetID"}, bi_vw_InspectionCompleted, {"AssetID"}, "bi_vw_InspectionCompleted", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each List.Max(let occurredDate = [Date_Occured] in
List.Select([bi_vw_InspectionCompleted][Inspection Completed], each _ < occurredDate))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"bi_vw_InspectionCompleted"})

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.