Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Using Today() in a custom column

Hi, I want a Pass/Fail column where if logical status - 6 and the record hasn't been updated for a year then it's a fail, otherwise a pass. I'm creating a custom column with the following:-

 

L16 = if [logical_status]=6 then if [DATE_UPDATED]<today()-365 then "Fail" else "Pass" else "Pass"

 

Now, it says there are no syntax errors but when I hit the OK button it then says it couldn't recognise the name "today". I'm sure it's really obvious but I'm struggling as a new powerquery user so any friendly advice would be welcomed :-)

Need Help

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous .

     

    The Query editor uses M Language and the syntax is different from DAX and excel you should use the syntax below instead of today

     

     

    Date.From(DateTime.LocalNow())

     

     

    Regards.

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks MFelix, that certainly helped me on the way and I'm now studying the PowerQuery M language in more depth.

       

      Could you have a look over this for me?

       

      =if [logical_status]=6 then if [DATE_UPDATED] < Date.From(DateTime.LocalNow()) - 365 then "Fail" else "Pass" else "Pass"

       

      Should be saying:

      if the status is 6 AND if the date updated is more than a year ago then it's a fail, else it's a pass.

       

      Am I on the right lines or should I be trying a different tack? It's coming up with "Error" in the new column although the syntax is passing muster. For info the status column is an integer and the date column is date only so I can't see where the problem lays!

       

      Thanks again for being kind to a noob ;-)

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi Anonymous ,

         

        You have two then on your statetment the syntax should be:

         

        =if [logical_status]=6 and [DATE_UPDATED] < Date.From(DateTime.LocalNow()) - 365 then "Fail" else "Pass"

         

        Regards,

        MFelix