Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

IF statement for DATEDIFF to handle an error

I'm calculating the number of DaysToCLose for a sales process. I have a CreatedDate and a ClosedDate to work with. My DAX is this: DaysToClose = DATEDIFF(Opportunities[CreatedDate],Opportunities[ClosedDate],DAY)

 

Sometimes the CreatedDate is after the CloseDate because of that's how sales works sometimes. When this happens I get and error in my column calculation: "In DATEDIFF function, the start date cannot be greater than the end date"

 

I need to create a solution to handle this error. I thought an IF statement could do it, but I don't know how to detect for the greater start date. When this error occurs DaysToClose =0. 

 

Thanks,

Jeff

Need Help
  • Sean's avatar
    Sean
    9 years ago

    Anonymous the SWITCH statement is internally converted into nested IFs but its much easier to write and read :smileyhappy:

     

    DaysToClose = 
SWITCH (
    TRUE (),
    'Opportunities'[ClosedDate] < 'Opportunities'[CreatedDate], -1 * DATEDIFF ( 'Opportunities'[ClosedDate], 'Opportunities'[CreatedDate], DAY ),
    'Opportunities'[ClosedDate] > 'Opportunities'[CreatedDate], DATEDIFF ( 'Opportunities'[CreatedDate], 'Opportunities'[ClosedDate], DAY ),
    0
)

    This should do it!

     

    EDIT: I guess I didn't see this in your original post => When this error occurs DaysToClose =0

    If by this you mean when the error occurs value should be zero you can modify the above column like this...

     

    DaysToClose 2 = 
SWITCH (
    TRUE (),
    ISBLANK('Opportunites'[CreatedDate]), 0,
    'Opportunites'[ClosedDate] > 'Opportunites'[CreatedDate], DATEDIFF ( 'Opportunites'[CreatedDate], 'Opportunites'[ClosedDate], DAY ),
    0
)

    The first formula will give you the differnce between Created and Closed:

    + difference if Created is before Closed and

    - difference if Closed is before Created

    plus you'll get blanks if either date is blank

     

    The second formula will give you ONLY the + difference if Created is before Closed all else will get 0.

     

2 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Anonymous the SWITCH statement is internally converted into nested IFs but its much easier to write and read :smileyhappy:

     

    DaysToClose = 
SWITCH (
    TRUE (),
    'Opportunities'[ClosedDate] < 'Opportunities'[CreatedDate], -1 * DATEDIFF ( 'Opportunities'[ClosedDate], 'Opportunities'[CreatedDate], DAY ),
    'Opportunities'[ClosedDate] > 'Opportunities'[CreatedDate], DATEDIFF ( 'Opportunities'[CreatedDate], 'Opportunities'[ClosedDate], DAY ),
    0
)

    This should do it!

     

    EDIT: I guess I didn't see this in your original post => When this error occurs DaysToClose =0

    If by this you mean when the error occurs value should be zero you can modify the above column like this...

     

    DaysToClose 2 = 
SWITCH (
    TRUE (),
    ISBLANK('Opportunites'[CreatedDate]), 0,
    'Opportunites'[ClosedDate] > 'Opportunites'[CreatedDate], DATEDIFF ( 'Opportunites'[CreatedDate], 'Opportunites'[ClosedDate], DAY ),
    0
)

    The first formula will give you the differnce between Created and Closed:

    + difference if Created is before Closed and

    - difference if Closed is before Created

    plus you'll get blanks if either date is blank

     

    The second formula will give you ONLY the + difference if Created is before Closed all else will get 0.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Sean Thanks. I'd never worked with the SWITCH function. An elegant solution.