Forum Discussion
IF ELSE Expression.Error: We cannot apply operator - to types Date and DateTime.
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..
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]))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]))
6 Replies
- MFelix
Super User
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
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]))
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!