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

Question

Hi All,&nbsp;Greetings!&nbsp;I am writing to seek your help to calculate pick-up time using DAX.I tried few solutions online, however, it doesn't consider the case identifier while calculating the time difference between two rows. I have the below dataset&nbsp;IndexCase IdentifierStepsFinal OutcomeStep StartedStep EndedPick up Time (Step Ended - Step Started time on the next stage)127771Step1Step 1 Completed6/14/21 16:306/14/21 17:004.0212696Step1Step 1 Completed6/11/21 12:306/11/21 13:008.0330997Step1Step 1 Completed6/11/21 15:006/11/21 17:004.0427771Step 2Rejected Back to Step 16/15/21 9:006/15/21 10:0015.5512696Step 2Rejected Back to Step 16/14/21 9:006/14/21 10:0017.0630997Step 2Rejected Back to Step 16/13/21 20:006/13/21 20:001.5712696Step1Step 1 Completed6/15/21 15:006/15/21 16:00&nbsp;827771Step1Step 1 Completed6/16/21 13:306/16/21 14:00&nbsp;930997Step1Step 1 Completed6/14/21 10:306/17/21 0:00&nbsp;&nbsp;This is how I want to create a relationship to derive pick-up time:&nbsp;&nbsp;&nbsp;Pick Up time calculation would have to consider the below logic:- Time calculation should only be considered until the last working day &amp; the next working day if the case is started (picked) on a weekend or on a bank holiday (in Ireland).- Only working hours (9 am - 9 PM) must be considered for the calculation of pick-up time.- If a case is worked from start - end on a weekend, the pick-up time for that case must be shown as 0.I am struggling very much to create a relationship between the dates and case identifiers to derive the desired result (pick-up time).&nbsp;Can I please ask for your help with my project.&nbsp;Sincerely,Raj

v-kelly-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;，
&nbsp;
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])&amp;&amp;'Table'[Index]&gt;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&lt;&gt;6||_weekdaystart&lt;&gt;7,
        _timediff1,0)),
    IF(_weekdaystart=6||_weekdaystart=7,
       IF(_weekdayend&lt;&gt;6||_weekdayend&lt;&gt;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]&gt;='Table'[Step Ended]&amp;&amp;'Calendar table'[Date]&lt;='Table'[_next step start]&amp;&amp;'Calendar table'[is weekend]=1))+0
var _gap=_datediff1-_weekend
Return
IF(_gap&lt;0,0,
   _datediff2-_weekend*24-_gap*12+'Table'[Turnaround])

And you will see:

For the related .pbix file,pls see attached.
&nbsp;
Best Regards,Kelly
Did I answer your question? Mark my post as a solution!

Forum Discussion

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

2 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

Calculating Differences

1. How to add two different calculated columns of two different table

Calculating day/time difference

Index	Case Identifier	Steps	Final Outcome	Step Started	Step Ended	Pick up Time (Step Ended - Step Started time on the next stage)
1	27771	Step1	Step 1 Completed	6/14/21 16:30	6/14/21 17:00	4.0
2	12696	Step1	Step 1 Completed	6/11/21 12:30	6/11/21 13:00	8.0
3	30997	Step1	Step 1 Completed	6/11/21 15:00	6/11/21 17:00	4.0
4	27771	Step 2	Rejected Back to Step 1	6/15/21 9:00	6/15/21 10:00	15.5
5	12696	Step 2	Rejected Back to Step 1	6/14/21 9:00	6/14/21 10:00	17.0
6	30997	Step 2	Rejected Back to Step 1	6/13/21 20:00	6/13/21 20:00	1.5
7	12696	Step1	Step 1 Completed	6/15/21 15:00	6/15/21 16:00
8	27771	Step1	Step 1 Completed	6/16/21 13:30	6/16/21 14:00
9	30997	Step1	Step 1 Completed	6/14/21 10:30	6/17/21 0:00