Forum Discussion

pminnov's avatar
pminnov
Icon for Helper II rankHelper II
4 years ago
Solved

Datediff calculation error with days or weeks

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 calculate...
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

pminnov , try that inside a measure

 

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

pminnov's avatar
pminnov
Icon for Helper II rankHelper II
4 years ago

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.