In DATEDIFF function the start date cannot be greatert than the end date

[dave_adlard]

This is tipping me over the edge. I have an excel spreadsheet that I have imported into Power BI.

I have filtered the result so only 3 records appear.

The next review date are the 9th, 12th and 12th again of March 2018 and the exposure start daes are  1st April 2018.

My new colum is

Days Betwn = DATEDIFF(

(DATEVALUE(Sheet1[Next Review Date])),
(DATEVALUE(Sheet1[Exposure Start Date]))
,DAY)

and I am hit with the error In DATEDIFF function the start date cannot be greatert than the end date

Which is obviously not the case. I have edited the query so the value are dates only or date/time and I still get the same error.

Any help would be appreciated.

Do I need to create it as a measure opposed to a column?

Please help?

[Zubair_Muhammad]

HI @dave_adlard

Try swapping the dates

Days Betwn =
DATEDIFF (
    ( DATEVALUE ( Sheet1[Exposure Start Date] ) ),
    ( DATEVALUE ( Sheet1[Next Review Date] ) ),
    DAY
)

[dave_adlard]

Thanks for getting back to me unfortunatey I get the same error. Any other ideas

[Zubair_Muhammad · Accepted solution]

HI @dave_adlard

Give this a shot

Days Betwn =
VAR MinDate =
    MIN (
        DATEVALUE ( Sheet1[Exposure Start Date] ),
        DATEVALUE ( Sheet1[Next Review Date] )
    )
VAR MaxDate =
    MAX (
        DATEVALUE ( Sheet1[Exposure Start Date] ),
        DATEVALUE ( Sheet1[Next Review Date] )
    )
RETURN
    DATEDIFF ( MinDate, MaxDate, DAY )

[dave_adlard]

that worked, thanks so much. Can you please explain to me?

[Zubair_Muhammad]

HI @dave_adlard

Actually DateDiff takes the following arguments

DATEDIFF(<start_date>, <end_date>, <interval>)  

Startdate can't be later than end_date ....otherwise it will throw an error..

Thats why we used Min and Max functions to ensure earlier date comes in the first argument and later date comes in the second argument of DATEDIFF