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

[Anonymous]

Hi All,

Greetings!

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

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

This is how I want to create a relationship to derive pick-up time:

Pick Up time calculation would have to consider the below logic:

- Time calculation should only be considered until the last working day & 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).

Can I please ask for your help with my project.

Sincerely,

Raj

[v-kelly-msft · Accepted solution]

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!

[Gabriel_Walkman]

Hi!

A lot of the latter questions are very trivial and you only need to research the very basics to achieve them. With bank holidays in Ireland I cannot help - maybe there's an online resource for those?

As for the main problem, you just need to add some simple Case Identifier iffing in a row by row method.