cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: IF statement for DATEDIFF to handle an error

@JeffatHero the SWITCH statement is internally converted into nested IFs but its much easier to write and read

```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 2
Super User

## Re: IF statement for DATEDIFF to handle an error

@JeffatHero the SWITCH statement is internally converted into nested IFs but its much easier to write and read

```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.

Highlighted
Frequent Visitor

## Re: IF statement for DATEDIFF to handle an error

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