custom column error

[eduardo]

Hi

I created a column with date difference, like this:

But there is an error. The difference is 30 days, not 29. I tried this

Thanks for some help

eduardo

[v-qiuyu-msft · Accepted solution]

Hi @eduardo,

In Query Editor, when we create a calculated column by using 'Add Custom Column', we need to write formula use Power Query Formula Language. In your scenario, to calculate the difference between start date and end date with day interval, you can use Duration.Days() and Duration.From() functions like below, you can also plus "1" at the end of the formula. For more information about function, see: Duration functions.

=Duration.Days(Duration.From([End date]-[Start Date]))

In your scenario, if you minus directly, it will return values which are not numbers, so you cann't plus '1'. But you can change this column data type to number then create another column to plus '1'.

Best Regards,
Qiuyun Yu

[eduardo]

Thanks @v-qiuyu-msft

I will test this solution later. 

ty

eduardo

[eduardo]

@v-qiuyu-msft

I found the Duration.TotalDays function and work.

thank you again