Calculating Time Difference in Hours between two Column and rows Pick-Up Time

Hi Anonymous ，

First create a calendar table;

Then create 3 columns as below:

_next step start = 
var _index=CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[Case Identifier]=EARLIER('Table'[Case Identifier])&&'Table'[Index]>EARLIER('Table'[Index])))
Return
CALCULATE(MAX('Table'[Step Started]),FILTER('Table','Table'[Index]=_index))

Turnaround = 
VAR _weekdaystart=WEEKDAY('Table'[Step Ended],2)
VAR _weekdayend=WEEKDAY('Table'[_next step start],2)
var _timediff1=DATEDIFF('Table'[_next step start],DATE(YEAR('Table'[_next step start]),MONTH('Table'[_next step start]),DAY('Table'[_next step start])+1),HOUR)-3
var _timediff2=DATEDIFF('Table'[Step Ended],DATE(YEAR('Table'[Step Ended]),MONTH('Table'[Step Ended]),DAY('Table'[Step Ended])+1),HOUR)-3
Return
     IF((_weekdayend=6||_weekdayend=7),
        IF(_weekdaystart=6||_weekdaystart=7,
        _timediff1-_timediff2,
        IF(_weekdaystart<>6||_weekdaystart<>7,
        _timediff1,0)),
    IF(_weekdaystart=6||_weekdaystart=7,
       IF(_weekdayend<>6||_weekdayend<>7,
       -_timediff2,0)))
 

Pick up Time (Step Ended - Step Started time on the next stage) = 
var _weekdayend=WEEKDAY('Table'[Step Ended],2)
var _weekdaystart=WEEKDAY('Table'[_next step start],2)
var _datediff1=IF(ISBLANK('Table'[_next step start]),BLANK(),DATEDIFF('Table'[Step Ended],'Table'[_next step start],DAY))
var _datediff2=IF(ISBLANK('Table'[_next step start]),BLANK(),DIVIDE(DATEDIFF('Table'[Step Ended],'Table'[_next step start],MINUTE),60))
var _weekend=CALCULATE(COUNTROWS('Calendar table'),FILTER(ALL('Calendar table'),'Calendar table'[Date]>='Table'[Step Ended]&&'Calendar table'[Date]<='Table'[_next step start]&&'Calendar table'[is weekend]=1))+0
var _gap=_datediff1-_weekend
Return
IF(_gap<0,0,
   _datediff2-_weekend*24-_gap*12+'Table'[Turnaround])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!