cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris1
Helper I
Helper I

Approximate Date Match with Multiple Criteria

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.

 

Chris1_0-1623857033982.png

 

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?

 

Chris1_2-1623858812012.png

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!

 

 

1 ACCEPTED SOLUTION

Hi, @Chris1 

Thanks for the feedback.

Please check the below.

 

Picture1.png


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @Chris1 

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.

 

Picture1.png

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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!

Hi, @Chris1 

Thanks for the feedback.

Please check the below.

 

Picture1.png


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Hi @Jihwan_Kim ,

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

Best regards,

Chris

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!