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...
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.

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.

    • hobbssj's avatar
      hobbssj
      Regular Visitor
      5 years ago

      Just a quick update, we ended up getting our DBA to create a custom view on on the database. 

       

      but I testted the Janey's soltuion and that worked as well 

       

      Thanks for everyone help