Compare dates from calculated columns

[Anonymous]

I have a report that I download once a day. In it there are due dates and completed dates for various topics. Once an item is completed, the due date then becomes one year out from the last completed date. What that in essence does is reset any items that were delinquent. So, at the begining of the month, I capture a report with all the due dates for that given month. I then download a daily copy and pull the last completed date from it to compare to the due date. This is helpful in yearly employee reviews to show who is getting their work done on time. That said, the lookup to get the last completed date works. Comparing it to the due date in the other table isn't working. I'm thinking maybe I need to compare it by using the lookup formula compared to the data and not calling the column. I'm thinking calling the column is technically bringing in a blank since it may not be looking at the actual cell as would be the case in Excel. Sometimes I miss Excel. 

This is my lookup formula:

Last Completion Date = LOOKUPVALUE('Training Report Daily Download'[Last Completion Date],'Training Report Daily Download'[Employee Name],'Training Report YTD'[Employee Name],'Training Report Daily Download'[Course],'Training Report YTD'[Course])

This is my comparison:

Adjusted Compliance Status = IF(AND('Training Report YTD'[Last Completion Date]=BLANK(),'Training Report YTD'[Due Date]=BLANK()),BLANK(),If('Training Report YTD'[Last Completion Date]>'Training Report YTD'[Due Date],"Delinquent",IF(AND('Training Report YTD'[Last Completion Date]=BLANK(),TODAY()>='Training Report YTD'[Due Date]),"Delinquent",IF(AND('Training Report YTD'[Last Completion Date]=BLANK(),TODAY()<='Training Report YTD'[Due Date]),"In Training Window","On Time"))))

Am I right that I can't technically compare the calculated (lookup) column in this fashion? 

The YTD is the file that is capturing the actual due dates. I remove the last completed column from it and replace it with the last completed from the daily download file via the lookup. 

[Anonymous]

Fantastic. I tried to do a switch statement but couldn't get it to work. I think it was the &&. Ironically, you missed one too, but I fixed it and this works perfect. Thanks. I'm posting it here. It's the last &&, you had a comma. 

Switch( True(),
isblank('Training Report YTD'[Last Completion Date]) && isblank('Training Report YTD'[Due Date]) , Blank() ,
'Training Report YTD'[Last Completion Date]>'Training Report YTD'[Due Date],"Delinquent" ,
isblank('Training Report YTD'[Last Completion Date]) && TODAY()>='Training Report YTD'[Due Date],"Delinquent",
isblank('Training Report YTD'[Last Completion Date]) && TODAY()<='Training Report YTD'[Due Date],"In Training Window","On Time")

[amitchandak · Accepted solution]

@Anonymous , Try like

Switch( True(),
isblank('Training Report YTD'[Last Completion Date]) && isblank('Training Report YTD'[Due Date]) , Blank() ,
'Training Report YTD'[Last Completion Date]>'Training Report YTD'[Due Date],"Delinquent" ,
isblank('Training Report YTD'[Last Completion Date]) && TODAY()>='Training Report YTD'[Due Date],"Delinquent",
isblank('Training Report YTD'[Last Completion Date]),TODAY()<='Training Report YTD'[Due Date],"In Training Window","On Time")

You should be able to use a calculated column

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8