Datediff Problem With Direct Query

[Anonymous]

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.

[marcelsmaglhaes]

@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]

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]

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

[Anonymous]

Only in DAX. I know that it works in Power Query, however, in this project, we only have Direct Query and it is unfeasible to use Power Query, as well as, we cannot solve it in the bank.
The fact is, it's basic arithmetic, which could be easily solved in ETL, Banco or Power Query. But the case specifies the resolution via DAX, as it is in Direct Query.
And the difficulty lies exclusively in DATEDIFF transforming thousands of lines in days, communicating with the Bank in DQ.

[marcelsmaglhaes]

And if you use a direct dax like dae1 - date2, without datediff function.

[Anonymous]

I tried too, it didn't work. I also tried to transform it into an integer, but it doesn't work via DQ. :s