Forum Discussion

DBATTIN's avatar
DBATTIN
Frequent Visitor
7 years ago
Solved

DATEDIFF Not Working in Custom Column

I am tyring to add the column as follows = Table.AddColumn(#"Added Custom", "OrderAge", each DATEDIFF ([DateTimeCreated],TODAY(),DAY))   But I get the meaasge    Expression.Error: The name 'DATE...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    DBATTIN I guess you have DateTimeCreated field as DateTime datatype which also needs to converted into Date type. So please try this...

     

    Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-DateTime.Date([DateTimeCreated])))
PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

DBATTIN You are trying to use both M-Language and DAX at one place and that's the reason for error. DATEDIFF is available in DAX but not in Power Query Editor.

 

If you want to achieve DATEDIFF in Power Query Editor then use Duration.Days(Duration.From([EndDate]-[StartDate]))) 

DBATTIN's avatar
DBATTIN
Frequent Visitor
7 years ago

PattemManoharIt now will not recognise the funtion Today() is that for the same reason? If so what function do I need to use for Today please?

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    DBATTIN Please use DateTime.Date(DateTime.LocalNow())

    • DBATTIN's avatar
      DBATTIN
      Frequent Visitor
      7 years ago

      PattemManoharthe formula is now

       

      OrderAge = Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-[DateTimeCreated]))

       

      This is now showing no syntax errors but returns an error value in the custom column. Have I done something wrong?

       

       

      • PattemManohar's avatar
        PattemManohar
        Community Champion
        7 years ago

        DBATTIN I guess you have DateTimeCreated field as DateTime datatype which also needs to converted into Date type. So please try this...

         

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