DATEDIFF formula in Power Bi

Question

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.

Answer 1

The DirectQuery connection type has limited support for DAX functions (only those that can be converted by the Power Query engine to native SQL can be used). I suspect that the DATEDIFF function is not supported.

Try replacing this formula with:

DATEVALUE([Date1]) - DATEVALUE([Date2])

Then format the result.