Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Datediff Problem With Direct Query

Hey guys!

I have a problem with the DATEDIFF formula in Power Bi.

I need to perform a DATEDIFF between two dates, which are in a fact table with more than 17 million rows.

The difference number between these dates can be with several decimal places, such as the difference between 1901 and 2010 in days.

That's for thousands of lines.

When I perform the calculation referencing the month, for example:

DATEDIFF([date1], [date2], month), works.

But when I set DATEDIFF([data1], [data2], day), it gives the OLE DB or ODBC error.

Remembering that I am making my query via Direct Query and it is essential that this information is up to date.

Does anyone know how to get around it? Thanks!

I expect the formula to return in days and not months.
When I try in months, it works, when I try in days, it doesn't work.

6 Replies

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Super User
    2 years ago

    Anonymous ,

    When using Direct Query, the performance of date calculations can also depend on the underlying database system. Ensure that the database query performance is optimized, and indexes are used effectively to avoid the odbc error. If your database is OK, so you should try creating a COLUMN in Power Query to perform that calculation instead. I think it will be more effective. 

    Kind Regards,
    Marcel

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Marcelo.

      Exceptionally in this project, I do not have and cannot optimize the database.
      I need to solve it within Power Bi.
      I've tried creating custom columns too, but it gives the same error.

       

      Thanks

      Keven

      • marcelsmaglhaes's avatar
        marcelsmaglhaes
        Super User
        2 years ago

        You tried to create on Power Query or using DAX? If you used DAX, try doing this using Power Query. If the error persists, than you can calculate the date differences as part of your ETL process before importing the data into Power BI. This would involve writing SQL queries or using database functions to perform the calculation...