Help on DAX Turnaround in Hours

[Alisea_MI]

Hi,

I would appreciate help on a DAX formula, that should calculate the number of hours between the sample received date and time and sample completed date and time. 

In the model I have separate Date table for the Dates and Time table for the hours, each of them has active and passive relationships with  some date columns from the fact table.


The formula I am working on is below together with the error message.

Wold be grateful for any tips on how to make it work.

[Alisea_MI]

Thanx, @amitchandak ! 
Unfortunately, I get this error: "the expression specified in the query is not a valid table expression".

I have also tested this: 

VAR _datediff = DATEDIFF(MAX(Labvantage[SampleReceivedDate]), MAX(Labvantage[SampleCompletedDate]), HOUR)
VAR _samplecompletedTime = MAXX(ALL(Labvantage), Labvantage[SampleCompletedTime]) * 24
VAR _samplereceivedTime = MAXX(ALL(Labvantage), Labvantage[SampleReceivedTime]) * 24
VAR _datetimedif = _datediff + _samplecompletedTime - _samplereceivedTime

RETURN
_DATETIMEDIF

Can it be so that it's not possible to create a measure on it?

[v-zhangti · Accepted solution]

Hi, @Alisea_MI 

This formula you tested works.

You can also try the following methods.

Measure 2 = 
Var _date1=DATEVALUE(SELECTEDVALUE(Labvantage[SampleReceivedDate])&" "&SELECTEDVALUE(Labvantage[SampleReceivedTime]))
Var _date2=DATEVALUE(SELECTEDVALUE(Labvantage[SampleCompletedDate])&" "&SELECTEDVALUE(Labvantage[SampleCompletedTime]))
Return
DATEDIFF(_date1,_date2,HOUR)

DATEVALUE function (DAX) - DAX | Microsoft Learn

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Alisea_MI , if they are from same table better to create a column

Datediff([StartDate]+[Start Time], [end Date]+[end Time], second)

or a measure

sumx(Table, Datediff([StartDate]+[Start Time], [end Date]+[end Time], second))

You can use new measure formatting to display it in time format

Power BI Dynamic Format Strings for Measures| Format without converting to text: https://youtu.be/qHALucET7N8