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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

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

Top Solution Authors