Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous 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.
@Anonymous 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |