Calculate the # of days between a column and today's date

[Whoule]

Hello,

I am trying to create a new column that returns the numbers of days between an existing column named Assigned On and todays date. Here is my formula: 

= Table.AddColumn(#"Filtered Rows", "Days since assigned", each if #"Request List"[Closed] = "True" then "" else DateTime.LocalNow()-[Assigned On])

The problem is the the new colum return ''error'' on each new row. (Expression.Error: A cyclic reference was encountered during evaluation.)

Thank you in advance for any help on this issue.

[artemus · Accepted solution]

The issue is probably related to #"Request List"[Closed], which depending on what that is may be the source of the cyclic reference. Are you sure it shouldn't just be [Closed]?

Also:

1. You want the [Closed] = "True" to be null, not "".

2. Change DateTime.LocalNow()-[Assigned On] to (DateTime.LocalNow()-[Assigned On]) / #duration(1, 0, 0, 0)

   This will change the value to number of days.