Forum Discussion

hobbssj's avatar
hobbssj
Regular Visitor
5 years ago
Solved

Trouble with calculated date column

Hello,
 
I'm trying to add a column to use in a visual. The column will be displaying one of two options if the date in the "modidate" is 3 days prior to the current date.
 
The modidate contains a datatime data type. and is contained in the tasks table
 
ticketupdated = IF(DATEDIFF(TASKS[MODIDATE];today();day); <3; True(UptoDate), False(Needs update))
 
With the above I am getting a sytax error as well as a pop up message that says cannot find modidate.
 
I figure I'm refrencing the SQL data wrong, but I'm not sure how to fix it, can anyone advise? or suggest a better way to do this?
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, hobbssj 

     

    You need to modify your code,Like this:

    = Table.AddColumn(#"Sorted Rows1", "ticketupdated", each if Date.From([date])>=Date.AddDays( Date.From(DateTime.LocalNow()),-3) and Date.From([date])<Date.From(DateTime.LocalNow())
then true 
else false)

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    hobbssj , this can be like

    ticketupdated = IF(DATEDIFF(TASKS[MODIDATE],today(),day) <3, True(), False())

    • hobbssj's avatar
      hobbssj
      Regular Visitor
      5 years ago

      Thanks,

       

      That made a difference  however its still not working 

      I added this to the custom column and it came out like this 

       

      = Table.AddColumn(#"Sorted Rows1", "ticketupdated", each ticketupdated = IF(DATEDIFF(TASKS[MODIDATE],today(),day) <3, True(), False()))

       

      and got 

      Expression.Error: The name 'ticketupdated' wasn't recognized. Make sure it's spelled correctly.

       

      I adjusted it 

       

      = Table.AddColumn(#"Sorted Rows1", "ticketupdated", IF(DATEDIFF(TASKS[MODIDATE],today(),day) <3, True(), False()))

       

      and now am getting 

       

      Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly

       

      any thoughts?

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, hobbssj 

         

        You need to modify your code,Like this:

        = Table.AddColumn(#"Sorted Rows1", "ticketupdated", each if Date.From([date])>=Date.AddDays( Date.From(DateTime.LocalNow()),-3) and Date.From([date])<Date.From(DateTime.LocalNow())
then true 
else false)

        If it doesn’t solve your problem, please feel free to ask me.

         

        Best Regards

        Janey Guo

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.