Forum Discussion

Brett007's avatar
Brett007
Helper III
6 years ago
Solved

Duration between two dates

I would like to calculate the difference between Today's Date and the Date in the [Assigned Date] Column. I wanted to present the answer in the form of days.    I used the following formula, but t...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi Brett007 , 

    You could try below M code to see whether it work or not

    Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-[Assigned Date]))

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Brett007's avatar
Brett007
Helper III
6 years ago

I tried, but I did not get a change in the results. 

 
 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Brett007 ,

 

I think the problem is not with the formula - it is in the data type. PBI generally does not support "silent" conversions.

From what I can see the [Assigned Date]  is type date and the DateTime.FixedLocalNow() returns type datetime. These types are not directly compatible and, most likely, you have an error like this:

Expression.Error: We cannot apply operator - to types DateTime and Date.
Details:
    Operator=-
    Left=10/03/2020 8:51:05 AM
    Right=2/01/2020

Either convert the [Assigned Date]  to type datetime or adjust your formula to force datetime to date type converstion:

Date.From(DateTime.FixedLocalNow())

Kind regards,

JB