Adding a column which calculates the number of days between a date in a column and today's date

[PaulCo]

I would like to create a column in a table which contains the number of days between a the date in a column and today's date. 

For instance I would like it to look like this if todays date was 6/20/2017

Estimated Close Date	Days until Close
6/30/2017	10
6/29/2017	9

I tried using the add custom column with the TODAY() function but it wasn't being recognised. 

Does anyone know the most efficient way to show the number of days?

Thanks

[v-ljerr-msft · Accepted solution]

Hi @PaulCo,

You should also be able to use TODAY() function to create calculate column to your table like below. 

Days until Close = Table1[Estimated Close Date] - TODAY()

Note: just replace 'Table1' with your real table name, and set the Data type to Whole Number for the new created calculate column.

Regards

[PaulCo]

This worked thanks 

[Mi2n]

Today() is a DAX function. You cannot use it in the Power Query part where you create a custom column. You will have to use the M language in this case.

Duration.Days(Duration.From(<Date field name> - DateTime.LocalNow))

Try something like this. You might have to replace the <Date field name> with your corresponding field name.