Approximate Date Match with Multiple Criteria

[Anonymous]

Greetings!

I have the 2 tables shown below (Contract & Usage). These tables are separate & not joined as they would have a "many-to-many" relationship. I am trying to derive the calculated column called "Contract Expired On" which is highlighted with the expected results.

I have used the following DAX I found on a blog post that is almost perfect, but I am off by 1 day when the mileage match is approximate. The exact matches are perfect. As you can see, the results below do not match the expected results (highlighted) from the above screenshot. Can anyone help me resolve this issue?

I'm also open to any insights on removing the 12/30/99 date. When the IF statement is true, I ran into issues of having different data types when I tried to have it return "N/A" or a "Blank" so I had to have it return a numeric value ("0" in my case). My current workaround is to conditionally format those dates out by changing the font color which may be the best/easiest way.

Thanks in advance!

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture if it is what you are looking for.

I am not sure which part is incorrect and which part is correct in your picture in the question.

[Anonymous]

Hi @Jihwan_Kim ,

My apologies for the confusion as I just realized I did not use the same name in my DAX calculated column as I did in the "Contract" table. 

For clarification, the column titled "Contract Expired On" DOES NOT currently exist in the "Contract" table. This is the column I am trying to write DAX for. The screenshot of my DAX code in my previous post should have started off with "Contract Expired On = IF(" instead of "Mileage Exceeded = IF("

In the code you sent back, the variable on Line 4 cannot be derived as the column name "Contract Expired On" does not yet exist....again, this is what I'm trying to derive. 

The highlighted values in the "Contract" table are the results I am expecting to get from the DAX formula (i.e. the contract expires on the date when the "Mileage" in the "Usage" table is greater than or equal to the "Contract Ending Mileage" in the "Contract" table.

Hopefully, this clears it up!

Best regards!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Thanks for the feedback.

Please check the below.

[Anonymous]

Hi @Jihwan_Kim ,

This is exactly what I was looking for....Thank you kindly!!

Best regards,

Chris