Forum Discussion

RobinH's avatar
RobinH
Helper I
6 years ago
Solved

DateTime.LocalNow() in Conditional column

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

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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()),

     

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

     

  • RobinH's avatar
    RobinH
    Helper I
    6 years ago

    Wow love this Community.

     

    Thank you all for this fast responses.

     

    Best Regards

    Robin

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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()),

     

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    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.