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!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

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