DateTime.LocalNow() in Conditional column

[RobinH]

Hey everyone

I try to create a column with the information if a ticket was closed out of the SLA or not.

I have a table with all tickets (closed and open ones). with the closed Tickets it's not an issue, if closed after the SLA date it's out of SLA. with the open ticket i need the todays date as reference (DateTime.LocalNow()).

So i created two columns:

#"Added Conditional Column5" = Table.AddColumn(#"Rounded Up", "SLA Meassure Date", each if [State] = "Closed" then [Update time] else if [State] = "Completed" then [Update time] else if [State] = "Aborted" then [Update time] else "DateTime.LocalNow()"),

#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Einhaltung SLA", each if [SLA] = null then "No SLA" else if [SLA] >= [date 2] then "within SLA" else if [SLA] < [date 2] then "Out of SLA" else null)

But it just insert DateTime.LocalNow(), also tried TODAY() with the same result.

Ist there a possibility a manage to use the actual Date as reference?

Tank you

Regards

Robin

[RobinH]

Wow love this Community.

Thank you all for this fast responses.

Best Regards

Robin

[v-xuding-msft]

Hi @RobinH ,

It is caused by double quotes("DateTime.LocalNow()"). If you use "", it will return the entire contents of anything inside them as text. So, you just need to remove them and then the function will work fine.

[Greg_Deckler · Accepted solution]

I believe you want:

#"Added Conditional Column5" = Table.AddColumn(#"Rounded Up", "SLA Meassure Date", each if [State] = "Closed" then [Update time] else if [State] = "Completed" then [Update time] else if [State] = "Aborted" then [Update time] else DateTime.LocalNow()),

[Mariusz]

Hi @RobinH 

Go to Added Conditional Column Step and In the Formula Bar, remove quotes from around DateTime.LocalNow() to convert it from text to a function.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn