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 =...
Need Help
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