topic Datediff calculation error with days or weeks in DAX Commands and Tips

Datediff calculation error with days or weeks

pminnov — Tue, 19 Jul 2022 21:02:06 GMT

Hi,

I've direct queried a datatable from Microsoft Dynamics through the dataverse function and wanted to calculate the difference in days between two dates (with one date column being a calculated date column containing date values from a related table). I used the datediff function but I only get a result if I use months or years as the interval. When I use days (or even weeks) I receive the following error message: OLE DB or ODBC error: [Expression Error]. We couldn't fold the expression into the data source. Please try a simpler expression.

Any help would be greatly appreciated. I can provide more detail if necessary.

Re: Datediff calculation error with days or weeks

amitchandak — Wed, 20 Jul 2022 03:45:04 GMT

pminnov , try that inside a measure

Sumx(Table, datediff([Start date],[End date], day) )

Re: Datediff calculation error with days or weeks

pminnov — Wed, 20 Jul 2022 17:11:23 GMT

Unfortunately your suggestion didn't work. If I try to use that formula in the form of a calculated column then SUMX isn't recognized as a function. The formula is accepted if I use it in a measure however when I try to show the results in a table visual I get the same error as before.

Re: Datediff calculation error with days or weeks

v-yanjiang-msft — Mon, 25 Jul 2022 06:17:47 GMT

Hi pminnov ,

According to the error message, by my research, I guess it's related to the DirectQuery mode, please try to use the import mode to check.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Datediff calculation error with days or weeks

pminnov — Mon, 25 Jul 2022 14:22:42 GMT

The expression does work however if months or years is used as the interval (just not weeks or days). The expression did work when I did a table merge then created a new column in the merged table and calculated the number of days between the two dates per row. I'm just confused as to why days or weeks was resulting in an error.

Re: Datediff calculation error with days or weeks

v-yanjiang-msft — Tue, 26 Jul 2022 03:14:56 GMT

Hi pminnov ,

Not sure why it only didn't work on days and weeks, but according to the error message, it's always related to the DirectQuery mode when there're complex data model or complex DAX. According to this blog, I guess it might be because when you use days and weeks in DAX, DAX query generated by the visuals on your report has not be folded into queries against your data source.

Additionally, you can also take a look at the discussions in the thread:

OLE DB or ODBC error: [Expression.Error] We couldn... - Microsoft Power BI Community

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Datediff calculation error with days or weeks

v-yanjiang-msft — Wed, 03 Aug 2022 10:19:56 GMT

Hi pminnov ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj