Forum Discussion
IF ELSE Expression.Error: We cannot apply operator - to types Date and DateTime.
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]))
Super UserHi 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]))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
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]))Thank you so much MFelix
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!