Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Datediff function seconds and minutes



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))


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))


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


Super User
Super User


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 (
        IF (
            ISBLANK ( RELATED ( mysql_mail_log[created] ) ),
            DATEDIFF ( mysql_reports[*NU], mysql_reports[first_report_expected], MINUTE )
    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. 

I sent my email in a private message

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

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

Helpful resources

September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors