Forum Discussion

annerogers's avatar
annerogers
Helper I
9 years ago
Solved

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 o...
Need Help
  • fhill's avatar
    fhill
    9 years ago

    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

     

annerogers's avatar
annerogers
Helper I
9 years ago

I just tried it, I am still getting the same error. :(

  • annerogers's avatar
    annerogers
    Helper I
    9 years ago

    Thank you fhill.

    I went there but I am not sure how to combine the calculation suggested in that post with the one I have built.  Any suggestions?

    This is what that post had:

    IF( ISBLANK(TABLE[MEASURE]), BLANK(),TABLE[MEASURE])

    OR 

    IF( ISBLANK(TABLE[MEASURE]), 0,TABLE[MEASURE])

     

    This is what my calc for the new column:

    IF(TABLE_PROGRAM[COLUMN_startdate]=TABLE_PROGRAM[COLUMN_enddate],0, DATEDIFF(TABLE_PROGRAM[COLUMN_startdate],TABLE_PROGRAM[COLUMN_enddate],DAY))

     

    Thank you for your help on this.

    • fhill's avatar
      fhill
      Resident Rockstar
      9 years ago

      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

       

      • annerogers's avatar
        annerogers
        Helper I
        9 years ago

        Fhill, You are the master!  It worked !!! :)

        Thank you soooo much. 

         

         

    • fhill's avatar
      fhill
      Resident Rockstar
      9 years ago

      Can you screen shot me some of your data containing the null values?  I want to duplciate on my side to troubelshoot..  FOrrest

      • annerogers's avatar
        annerogers
        Helper I
        9 years ago

        Here you go.  Thank you for helping me.