Trouble with calculated date column

[hobbssj]

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?

[amitchandak]

@hobbssj , this can be like

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

[hobbssj]

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 · Accepted solution]

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]

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