Forum Discussion

sffc's avatar
sffc
Helper I
4 years ago
Solved

DATEDIFF Incorrect Conversion Output

The DATEDIFF calucation is outputting incorrect values as can be seen in the screen capture below.  Is there a specific reason why that anyone is aware of, or is there a more accurate method to get the count of days between Date A - Date B?

 

 

Need Help
  • KNP's avatar
    KNP
    4 years ago

    I've only got working results also.

    See updated PBIX attached. Page 2.

    The first place I'd be looking is the data types for the dates and see if there are any conversion issues/locale issues, but I doubt that is going to account for such significant differences.

     

  • sffc's avatar
    sffc
    4 years ago

    Add custom column:

    = Duration.Days([DecommissionDate]-[DateFirstUse]))
    Power BI doesn't like calculated date columns with a SQL datasource.  Adding a custom column in Power Query produces correct calculations for all values.

17 Replies

  • ALLUREAN's avatar
    ALLUREAN
    Solution Sage
    4 years ago

    Hi, sffc 

    Try creating a calendar table with complete dates, then connect it to the fact table (Equipment) and use the function. 

    Sample calendar table:

    Date = ADDCOLUMNS(CALENDAR(
    DATE(YEAR(MIN(%YourTable[YourColumn]%)), 1, 1),
    DATE(YEAR(MAX(%YourTable[YourColumn]%)), 12, 1)),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "MonthNo", FORMAT ( [Date], "MM" ),
    "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonth", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthShort", FORMAT ( [Date], "mmm" ),
    "MonthLong", FORMAT ( [Date], "mmmm" ),
    "WeekNo", WEEKDAY ( [Date] ),
    "WeekDay", FORMAT ( [Date], "dddd" ),
    "WeekDayShort", FORMAT ( [Date], "dddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date],"Q" )
    )
    • sffc's avatar
      sffc
      Helper I
      4 years ago

      Hi - thanks for the response and idea.  I can give that a try, but can you please confirm what you mean by "complete dates"?  I've tried with short and long dates that are complete, but get the same results - i.e., Wednesday, December 15, 2015  ->  12/15/2015, etc. 

      • ALLUREAN's avatar
        ALLUREAN
        Solution Sage
        4 years ago

        Can you try this:

        TimeInService = SUMX('Equipment', DATEDIFF('Equipment'[DateFirstUse].[Day], 'Equipment'[DecommisionDate].[Day], DAY))

  • sffc's avatar
    sffc
    Helper I
    4 years ago

    The results for the above function can be seen in the screen capture below that are subtsantially higher -


     

    • ALLUREAN's avatar
      ALLUREAN
      Solution Sage
      4 years ago

      Not sure that will help, but the last .[Date] should be .[Day]. It will be good if you provide some file if not contain any sensitive info

      • sffc's avatar
        sffc
        Helper I
        4 years ago

        I must've changed that having to retype some of the table names, but I changed it to .[Day] and still get incorrect results.

  • KNP's avatar
    KNP
    Super User
    4 years ago

    DateDiff should work fine.

    Need more detail.

    Is that a measure or calculated column?

    Can you include some actual data? (not a screen shot)

    A screen shot of your data model may also help.

     

    • sffc's avatar
      sffc
      Helper I
      4 years ago

      This is in a calculated column.  I don't see a way to use columns in measures, so I've been using calculated columns:

      timeInService = DATEDIFF(Equipment[DateFirstUse], Equipment[DecommissionDate], DAY)
       
      I keep getting an HTML error upon posting the reply when trying to paste the table in the response, and I don't see an option to necessarily import or attach anything.  What is the best way to supply the sample data in this form?

      My apologies, but I cannot share a screen shot of the model.  

       

      The DATEDIF() function in Excel calculates the values correctly, so this appears to be an issue specific to Power BI for some reason.  In Power BI, some of the values are correctly coverted, but a majority of them are incorrect.

      • KNP's avatar
        KNP
        Super User
        4 years ago

        You should just be able to copy and paste from excel.

        Alternately, share in google drive or similar.

         

        This was copy/pasted from excel...

        DateStart DateEnd
        18/05/2019 4/12/2011
        2/09/2011 1/07/2019
        11/10/2010 11/08/2013
        28/09/2010 20/01/2017
        4/09/2011 19/09/2018
        17/07/2013 7/12/2009
        7/08/2013 10/02/2017
        5/06/2014 14/03/2010
        15/02/2019 10/08/2021
        21/05/2013 4/06/2010
        5/07/2019 20/01/2012
        17/12/2013 17/07/2021
        15/11/2012 14/05/2018
        16/02/2017 3/01/2014