Look up another table with date range

[Anonymous]

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 - 

Driver Employee ID	Days Reported	Odometer	Personal Miles	Business Miles	Total Miles	Date
1111	31	79397	108	579	687	12/1/2019
2222	31	72987	144	878	1022	1/1/2020
1111	30	91909	350	414	764	11/1/2019
4444	0		0	0	0	2/1/2020
5555	31	61025	297	975	1272	10/1/2019

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.

Employee ID	Exception Type	Start Date	Expiration Date
1111	1	1/1/2018	1/1/2021
2222	2	1/1/2019	7/1/2020

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.

[Greg_Deckler]

Well, if the data range doesn't matter:

VAR __Exempt = DISTINCT('Table'[Employee ID])

IF(MAX([PRSN_ID])) IN __Exempt,"Excempt", <calculation goes here>

If dates do matter, then I feel there is missing information that would be required to complete a suitable answer. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler Thanks for the speedy response! I've updated my question. Is there anyway you can share with me what happens if we have to consider the date range as well?

Best Regards