Forum Discussion
DATEDIFF calculation error
Hi all, I am trying to use date diff to calculate the number of days between start date and end date. I keep getting the error below, but when I go back into the data model to look across the rows of data the start date is always before (or less than) the end date. so why would I get the error "the start date cannot be greater than the end date"? Appreciate any help.
OK, good news! DateDiff doesn't freak out over Nulls, it just returns another Null. (See screen shot 3). It also has no issues with days equal to each other... (Sorry for those wrong paths). (also screen shot 3).
This is my formula if you want NULLS to pass thru for no End Date. (Best practics)
DateDiff2 = IF(Table1[Start_Date]<=Table1[End_Date],DATEDIFF(Table1[Start_Date],Table1[End_Date],DAY),(DATEDIFF(Table1[End_Date],Table1[Start_Date],DAY)*-1))
This is another option if you want 0 instead of NULL for a null end date.
DateDiff2 = IF(ISBLANK(Table1[End_Date]),0,IF(Table1[Start_Date]<=Table1[End_Date],DATEDIFF(Table1[Start_Date],Table1[End_Date],DAY),(DATEDIFF(Table1[End_Date],Table1[Start_Date],DAY)*-1)))
See if this works and sort the result ascending... i'm curious what PowerBI is seeing that's still making it think the dates are reveresed... (Please let me know, I'm sucked into the mystery now...)
FOrrest
9 Replies
Double check you don't have any Start Date = End Date situations. If you do, you can easily add an IF(StartDate = EndDate , 0 , <Your current DateDiff Logic>)
I do have some that are the same. There are also some where there is not yet an end date. Would I do something different for them?
I will try making the change above. Thank you for your help.
Anne
I just tried it, I am still getting the same error. :(
I'm thinking the 'null' or blank End Dates might be your problem.... See if this posting helps to build an IF statement around those blank values..?
https://community.powerbi.com/t5/Desktop/function-isnull/td-p/12121
Forrest
