IF ELSE Expression.Error: We cannot apply operator - to types Date and DateTime.

[Anonymous]

Hi,

I am new to Power BI. I have been having a hard time trying to replicate this formula in Power BI:

=IF([@[Return Approved Date]]="","NO APPROVAL DATE",ROUNDDOWN($BC$1-[@[Return Approved Date]],0)) 

where $BC$1 is TODAY()

I added a custom column using this formula 

However, the value of that returns Error only.

Please help..

[MFelix · Accepted solution]

Hi @Anonymous ,

Is today a column on your data model? you need to use a DATEDIFF formula that in M language is DURATION.

check the formulas on the link below:

https://docs.microsoft.com/en-us/powerquery-m/duration-functions

Also if you want to pick todays date you need to use 

DateTime.LocalNow() 

 Try the following code:

if  [Return Approved Date] = null then "No Approved date" else Duration.Days (Duration.From( #date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow()) )


- [Return Approved Date]))

[MFelix · Accepted solution]

Hi @Anonymous ,

Is today a column on your data model? you need to use a DATEDIFF formula that in M language is DURATION.

check the formulas on the link below:

https://docs.microsoft.com/en-us/powerquery-m/duration-functions

Also if you want to pick todays date you need to use 

DateTime.LocalNow() 

 Try the following code:

if  [Return Approved Date] = null then "No Approved date" else Duration.Days (Duration.From( #date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow()) )


- [Return Approved Date]))

[Anonymous]

Hi @MFelix , do you mind if i ask you to help me understanding the code below:

Duration.Days (Duration.From( #date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow()) )

like can you please how it works step by step?

thanks!

[Anonymous]

This actually worked. Thank you! Before I press the accept as solution. May i know if using Today column will work too? I have created a column for today which is 

DateTime.LocalNow() 

[MFelix · Accepted solution]

Hi @Anonymous ,

Using the Today column will also work the only question is that if you are using that column only for this calculation this will have an increase of data in your model, so there is no need to have this additional column on the model since you can do the calculation to make use of the today column redo the formula to:

if  [Return Approved Date] = null then "No Approved date" else Duration.Days (Duration.From( [Today] - [Return Approved Date]))

[Anonymous]

Hello @MFelix Can you also explain to me the logic behind the code below:

Duration.Days (Duration.From( #date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow()) )

I just need more understanding how it works.

Thanks!

[Anonymous]

Thank you so much @MFelix