Comparing dates with blanks with IF formula

[Anonymous]

Hello everyone,

Recently I started using Power BI and I have managed to create a few visuals, which I am happy with. I have also worked with some DAX code already. However, I am now trying to compare two columns, which I encounter some problems with and I wonder if you BI experts can help me out.

In my sheet I have two colums: contract start and contract end, with dates of contracts of employees. So I have employees who have already left the company (contract ended) and some who are still working at the company. In my visuals I want to only show the data (employee count, FTE etc.) of the employees who still work for the company, not ex-employees.

To do this I have made a new column with the following formula, so I can use it as a filter for the visuals:

Contract ended? = IF('Datadump PSA'[contract end]<=TODAY();"Yes";"No")

And this works great. However: some employees do not have a contract end date filled in because they are not bound to an end date. The problem is that when there is no contract end date filled in, the formula displays it as a “Yes” since a blank cell is <= todays date.

Can somebody tell me how to exclude the blanks from the formula, or has any other way to handle this specific situation?

Thank you for your time.

Best regards,

AliBI

[PattemManohar · Accepted solution]

@Anonymous If you want to exclude blank values totally from your logic without flagging anything then use

Contract ended? = IF(NOT ISBLANK('Datadump PSA'[contract end]),IF('Datadump PSA'[contract end]<=TODAY();"Yes";"No"))

Or if you want to flag the blank values as Contract Ended = No then use

Contract ended? = IF(NOT ISBLANK('Datadump PSA'[contract end]),IF('Datadump PSA'[contract end]<=TODAY();"Yes";"No"),"No")

Note - It will be always helpful if you can post your query along with some sample data to test on and to provide you an accurate solution.

[Anonymous]

Hello PattemMonahar,

Thank you so much for the formulas. The second one was exactly what I was looking for, and it accomplished what I wanted. I have accepted your answer as a solution.

Thanks again.

Ali