cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
svanwetswinkel
Frequent Visitor

Datediff function seconds and minutes

Hi, 

 

When I calculate a datediff in hours I haven't any problem:

*Countdown (hours) = IF(ISBLANK(RELATED(mysql_mail_log[created])),DATEDIFF(mysql_reports[*NU],mysql_reports[first_report_expected],HOUR))
svanwetswinkel_0-1651781854227.png

 

When I try to calculate the datediff in minutes or seconds, I receive the mesage 'Can't display the visual -> OLE DB or EDBC error: [Expression.Error] We couldn't fold the expression to the data source. 
 
*Countdown (minutes) = IF(ISBLANK(RELATED(mysql_mail_log[created])),DATEDIFF(mysql_reports[*NU],mysql_reports[first_report_expected],MINUTE))
 
svanwetswinkel_1-1651781943368.png

 

 Anyone knows what is wrong?
 
I'm using SQL direct query data. 
 

 

6 REPLIES 6
tamerj1
Super User
Super User

@svanwetswinkel 

Strange that the HOUR works! Creating time related calculated columns is not allowed in direct query. You have to create measures only

how I calculate 'hh:mm' or decimal hours with only measurement and without column? I want a value for every row without a calculation like sum, average, ...

Hi @svanwetswinkel 
This might require further formatting

Countdown (minutes) =
VAR TimeDifference =
    SUMX (
        mysql_reports,
        IF (
            ISBLANK ( RELATED ( mysql_mail_log[created] ) ),
            DATEDIFF ( mysql_reports[*NU], mysql_reports[first_report_expected], MINUTE )
        )
    )
RETURN
    QUOTIENT ( TimeDifference, 60 ) & ":"
        & MOD ( TimeDifference, 60 )

Would be great if you can provide sample file

I don't succeed 😞

Is it possible to mail you the file? 

I hope you can give me the solution. 

@svanwetswinkel 
I sent my email in a private message

@svanwetswinkel 
Even more simple is just to substract and select "Short Time" data type manually.

Countdown (minutes) =
SUMX (
    mysql_reports,
    IF (
        ISBLANK ( RELATED ( mysql_mail_log[created] ) ),
        mysql_reports[first_report_expected] - mysql_reports[*NU]
    )
)

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors