DATEDIFF question

[skempson]

Hi - 

I am trying to calculate the difference between 2 dates and this is working to a certain point.

DaytoEnd = IF(ISBLANK(SUM(proj_assessmentSet[ems_datecompleted])), 0, 1.0* SUM(proj_assessmentSet[ems_datecompleted]) - SUM(proj_assessmentSet[proj_StartDate]))

However, some dates are showing 0 even when there are 2 valid dates. Example:

7/07/2013 16:00 -  30/03/2016 7:31 = 997 (correct)

3/07/2013 16:00  - 25/02/2016 3:54 = 0 (incorrect)

In Excel the datedif function fro the second example give the correct value of 967.

Any ideas?

[Anonymous]

Hi skempson,

Based on test, it works well on my side, below is the test steps.

1. Create a date table.( date Between 2000~ today)

Excel formula:

DateStart =RANDBETWEEN(DATE(2000,1,1),TODAY())

DateEnd =A2+RANDBETWEEN(1,IF((YEAR(TODAY())-YEAR(A2))*365>0,(YEAR(TODAY())-YEAR(A2))*365,1))

1. Test formulas.

Calculate column:

DateDiff =

VAR minDate=MIN([DateStart],[DateEnd])

VAR maxDate=MAX([DateStart],[DateEnd])

return

DATEDIFF(minDate,maxDate,DAY)

Measures:

DaytoEnd =IF(ISBLANK(SUM(Sheet1[DateEnd])), 0, 1.0* SUM(Sheet1[DateEnd]) - SUM(Sheet1[DateStart]))

Diff = DATEDIFF(MIN(VALUES(Sheet1[DateStart]),VALUES(Sheet1[DateEnd])) ,MAX(VALUES(Sheet1[DateStart]),VALUES(Sheet1[DateEnd])) ,DAY)

Result:

 

Since I can’t reproduce your issue, can you share us a pbix file?

Regards,

Xiaoxin Sheng