If the date is less than today with NULL

[_jaime_]

Hi all!

I am total new here.

I have the date data as below:

I want to add a custom column

If the IEP Apporved Forecast/Actual is less than today then "yes" else "no" (include NULL).

I've tried 

IF([#"IEP Approved Forecast/Actual"] <= TODAY(),"yes","no"), and error message showed up.

How can I do this?

Thanks in advance! 🙂

[Ciotti]

Hi, 

I have tried the above but it is returning error because I have missing dates in a date column displaying as null. Other dates work as expected. 

is there a way to include null or do I need to create a new column and replacing the nulls?

Thanks 

[BA_Pete]

Hi @Ciotti ,

You can just add another condition into to IF statement, something like this:

if [YourColumn] = null then null
else if [YourColumn] <= Date.From(DateTime.LocalNow()) then "yes"
else "no"

Pete

[BA_Pete · Accepted solution]

Hi

I believe the following should work in power query:

MyColumn =
if [IEP Approved Forecast/Actual] <= Date.From(DateTime.LocalNow())
then "yes"
else "no"

Pete

[_jaime_]

I did the query as below, and it worked! Thank you very much! 🙂

MyColumn =

If [#"IEP Approved Forecast/Actual"] <= Date.From(DateTime.LocalNow())
then "yes"
else "no"

[amitchandak]

Are you doing in edit Query mode as a custom column or create a new column outside edit query.

For power query refer

https://community.powerbi.com/t5/Desktop/IF-Statement-using-Power-Query/td-p/122030

I think it should be like

IF([#IEP Approved Forecast/Actual] <= TODAY(),"yes","no"),

[_jaime_]

I am doing in power query editer mode. Thank you!

[BA_Pete]

Did this solve the issue?
Please mark as the solution if it did.

Thanks