IF and Lookup Value off of two different sources

[KEH1987]

I have this formula from Excel =IF(VLOOKUP(AP6, maxduration, 2, FALSE)="na", (AI6-AH6), VLOOKUP(AP6, maxduration, 2, FALSE)

This AI and AH are policy expiration date and policy effective date.  I'm trying to find the date difference between the AH6 expiration date and AI6 effective date.  However, there are certain programs that have a max duration that is less than the date difference.  Those programs have a max limit of duration, so even if the difference in dates is 1600 days, they max out at 700.  The rest of the data that doesn't have a max duration is labled as "na".  If it's "na" then I just want the date difference between the expiration date and effective date. 

I have the two sources connected on "program", one source is Excel which has the max days on it as well as the programs with "na" on them, and the other source is SQL.  How can  I look up these certain programs and set their max duration based upon my excel source in powerbi and based upon the forumla above?

[Anonymous]

Hi @KEH1987 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Measure = 
var _day=DATEDIFF(MAX('Table'[policy expiration date]),MAX('Table'[policy effective date]),DAY)
var _max=INT(IF(MAX('Table'[max duration])="na",BLANK(),MAX('Table'[max duration])))
RETURN IF(MAX('Table'[max duration])="na",_day,IF(_day<=_max,_day,_max))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[KEH1987]

[KEH1987]

It's still not capping R&W at 730.  I have a query that contains all the data and then I have an excel sheet that has the max duration on it.  I connected the two tables on "program" which is R&W, Tax, etc. but it's not capping my values as you can see in the screenshot.

[amitchandak]

@KEH1987 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

But Earlier can help

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8