Look up another table with date range

Hi All,

The problem - 

Our sales team gets company-provided vehicles. Each month, we get an excel file that has information on the number miles of being driven by various employees in these leased vehicles.

A snippet of the merged files - 

I have to create a report that calculates –

1. The projected Business miles of drivers for a year based on a certain time period. I created a measure, [Annualized Cumulative Business Miles] = DIVIDE('fleet'[M Total Business Miles] * 365,'fleet'[M Total Days Reported],0) for the same.  Note, [M Total Business Miles] and [M Total Days Reported] are also measures.

1. A calculated column, [Compliance Status] = IF('fleet'[Annualized Cumulative Business Miles] >= 12000, "Compliant", "Not-Compliant")

Basically, we need to check if it is worth giving employees leased vehicles. If they are projected to completed 12,000 miles in a year, their [Compliance Status] is “Compliant”. Otherwise they are “Not-Compliant”.

This table is called ‘Fleet’.

First Part

The issue is that there is another table – ‘Exception Drivers’ -  that has a list of drivers  who are not to be considered for the above mentioned compliance.

We are provided with a [Start Date] and an [End Date]. Anytime a driver falls within this date range, he/she should be considered as an "Exception". So now, my calc. column – [Compliance Status] - will have 3 values, i.e., "Compliant", "Non-Compliant" and "Exception".

Exception Table (it'll have more values filled in)

Thing to note – Fleet table and Exception Drivers table are linked by the Driver/Emp ID.

The mapping from ‘Fleet’ to ‘Exception Drivers’ is *:1 (Many-to-1)

My Question –

How do I look up the ‘Exception Driver’ table and return the class of “Exception” into the column [Compliance Status] which is part of the ‘Fleet’ Table. Also, I must keep in mind the [Start Date] and [End Date] in the ‘Exception Driver’ table.

Truly appreciate your patience and comments.

PS: I made changes after Greg_Deckler  suggested I make it simpler for users to understand the problem. Tried to include a a few rows of data and the measures as well. Hope this helps.