Forum Discussion

Brett007's avatar
Brett007
Icon for Helper III rankHelper 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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Probably needs to be something like:

 

Number.From([Date2] - [Date1]) + 1

 

?

  • Brett007's avatar
    Brett007
    Icon for Helper III rankHelper 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