cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ihartdata Frequent Visitor
Frequent Visitor

DATEDIFF with blanks and wonky dates - need help!

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:

Sample Data.PNG

 

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. :-)

1 ACCEPTED SOLUTION

Accepted Solutions
thomasronn Regular Visitor
Regular Visitor

Re: DATEDIFF with blanks and wonky dates - need help!

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

3 REPLIES 3
thomasronn Regular Visitor
Regular Visitor

Re: DATEDIFF with blanks and wonky dates - need help!

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

Phil_Seamark Super Contributor
Super Contributor

Re: DATEDIFF with blanks and wonky dates - need help!

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])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

ihartdata Frequent Visitor
Frequent Visitor

Re: DATEDIFF with blanks and wonky dates - need help!

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.