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

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