topic Re: Datediff function seconds and minutes in DAX Commands and Tips

Datediff function seconds and minutes

Anonymous — Thu, 05 May 2022 20:21:42 GMT

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

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.

Re: Datediff function seconds and minutes

tamerj1 — Fri, 06 May 2022 08:29:27 GMT

Anonymous

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

Re: Datediff function seconds and minutes

Anonymous — Wed, 11 May 2022 09:05:07 GMT

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, ...

Re: Datediff function seconds and minutes

tamerj1 — Wed, 11 May 2022 10:19:26 GMT

Hi Anonymous
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

Re: Datediff function seconds and minutes

tamerj1 — Wed, 11 May 2022 10:24:14 GMT

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

Re: Datediff function seconds and minutes

Anonymous — Mon, 16 May 2022 10:10:38 GMT

I don't succeed 😞

Is it possible to mail you the file?

I hope you can give me the solution.

Re: Datediff function seconds and minutes

tamerj1 — Mon, 16 May 2022 10:24:26 GMT

Anonymous
I sent my email in a private message