Using Today() in a custom column

[Anonymous]

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 🙂

[MFelix]

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]

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]

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