Forum Discussion

ihartdata's avatar
ihartdata
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago
Solved

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:

 

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

Need Help
  • thomasronn's avatar
    thomasronn
    8 years ago

    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

  • thomasronn's avatar
    thomasronn
    Icon for Resolver I rankResolver I
    8 years ago

    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's avatar
      ihartdata
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      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.

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

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