Substract start time column from end time column based on date and sequence

[Anonymous]

Hello everyone,

I would like to subtract "start time" from "end time" based on date and sequence. 

we visit customers based on a given sequence within a certain day. I would like to calculate the time spent between customers. 

It will be= Start_Time-End_Time (Based on date column and sequence column)

Date	Customer	Visiting_Sequence	Start_Time	End_Time
10/22/2021	a	1	13:56	13:59
10/22/2021	b	2	14:10	14:20
10/22/2021	c	3	14:31	14:44
10/22/2021	d	4	14:50	14:55
10/22/2021	e	5	14:59	15:09
10/22/2021	f	6	15:16	15:20

This is the expected value I would like to get.

Date	Customer	Visiting_Sequence	Start_Time	End_Time	Expected_Value
10/22/2021	a	1	13:56	13:59
10/22/2021	b	2	14:10	14:20	0:11
10/22/2021	c	3	14:31	14:44	0:11
10/22/2021	d	4	14:50	14:55	0:06
10/22/2021	e	5	14:59	15:09	0:04
10/22/2021	f	6	15:16	15:20	0:07

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

Measure = 
var _Seq=MAX('Table'[Visiting_Sequence])
var _last_Seq=_Seq-1
var _endOfLast=CALCULATE(MAX('Table'[End_Time]),FILTER(ALL('Table'),'Table'[Visiting_Sequence]=_last_Seq))

var _result=IF(ISBLANK(_endOfLast),BLANK(),
FORMAT(MAX('Table'[Start_Time])-_endOfLast,"hh:mm")
)

return _result

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[amitchandak]

@Anonymous , Is it not

a new column =

datediff([end_time],[Start_time], second)+1

[Anonymous]

Thank you for your response
is it possible to include the sequence column to calculate? there are a lot of customers and a lot of sales reps. and the sequence on the table is kind of mixed up. the only thing that can be used to calculate is the date and the sequence number, for each sales reps.