Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Proud to be a PBI Community Champion
@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.
Proud to be a PBI Community Champion
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |