adding a calculated field to a paginated report some of my results are off by 1

[2hikenature]

I have 2 date columns (created & completed) I need to pull the difference between the columns. Both columns are set up with the date & time format.

So I went into Paginated reports and added a calculated field and added the expression:

=DateDiff(DateInterval.Day,Fields!DateCompleted.Value,Fields!Created.Value)

Which I throught worked but upon looking at the results I am noticing that some of my results are off by 1. After looking closer at the data I believe the time in the format is causing the calculation to be off. 

[Anonymous · Accepted solution]

Hi @2hikenature ,

The 'DateDiff' function calculates the difference in days between two dates, and if there's a time component involved, it can cause the difference to be off by one day depending on the time values of the 'Created' and 'DateCompleted' fields.

To address this issue, you can modify your calculated field expression to first convert the datetime values to dates (removing the time component) before calculating the difference. This way, the calculation focuses solely on the date parts, ensuring accurate day difference calculations regardless of the time values.

=DateDiff(DateInterval.Day, DateValue(Fields!DateCompleted.Value), DateValue(Fields!Created.Value))

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[2hikenature]

Yes!! Thank you so much!