DATEDIFF with blanks and wonky dates - need help!

[ihartdata]

Hi,

I am trying to create a calculated column to provide the number of days between an inbound request and an outbound invitation. All of the data needed is now contained in one table. Unfortunately some of the dates are blank and other rows have the outbound invitation (end date) before the inbound request (start date) and that is throwing errors. I solved for the Blank dates and I solved for the End Date before the Start Date, and now I am getting an error that says the Start Date can't be greater than the end date. Which is confusing because I think it means the same thing: End Date<Start Date = Start Date>End Date??

Here is sample data:

Here is the DAX I have wrote so far:

Days to Enroll = IF(OR(ISBLANK('Table'[Start Date]),ISBLANK('Table'[End Date])),3,(IF(OR('Table'[End Date]<'Table'[Start Date],'Table'[Start Date]>'Table'[End Date]),-1*DATEDIFF('Table'[Start Date],'Table'[End Date],DAY),DATEDIFF('Table'[Start Date],'Table'[End Date],DAY))))

Here is the Error I am receiving: "In DATEDIFF function, the start date cannot be greater than the end date"

Help me! thank you. 🙂

[Phil_Seamark]

Hi there

The DATEDIFF function will throw and error if it things the first of the two parameters is larger than the second parameter,

Are you just after the number of days between each date?

If so, covert the dates to INTand then subtract the values.  This will give negative numbers for inverse dates (but no error)

CalcColumn = INT('Table'[End Date] - 'Table'[Start Date])

[thomasronn · Accepted solution]

Swap start and end date in DATEDIFF() when Start Date i greather than End Date.

Change this part 

...-1*DATEDIFF('Table'[Start Date],'Table'[End Date],DAY)...

to

...-1*DATEDIFF('Table'[End Date],'Table'[Start Date],DAY)...

and yes, End Date<Start Date = Start Date>End Date. So you don't need to check both

[ihartdata]

Thomas!!! That worked. Thank you, thank you, thank you. I have been racking my brains for way too long. Super appreciated! Have a lovely weekend.