Calculating Time Difference

[ljmanayon]

Hi,

Hope someone can help me on this, am a noob on this. Anyways, I just simply want to compute the time difference between "Serving" and "Done" Remarks.  Here's my sample data.

Date	Time	Service ID	Product	Remarks
Aug, 1 2021	8:00:00AM	B001	Bills	New Entry
Aug, 1 2021	8:05:00AM	B001	Bills	Serving
Aug, 1 2021	8:07:00AM	B001	Bills	Done
Aug, 1 2021	8:10:00AM	B002	Bills	New Entry
Aug, 1 2021	8:15:00AM	B002	Bills	Serving
Aug, 1 2021	8:20:00AM	B002	Bills	Done
Aug, 1 2021	8:30:00AM	C001	Cargo	New Entry
Aug, 1 2021	8:32:00AM	C001	Cargo	Serving
Aug, 1 2021	9:40:00AM	C001	Cargo	Done

This is the result that I want. I want the time difference reflects on the "Serving" Remarks.

Date	Time	Service ID	Product	Remarks	Processing Time
Aug, 1 2021	8:00:00AM	B001	Bills	New Entry
Aug, 1 2021	8:05:00AM	B001	Bills	Serving	2mins
Aug, 1 2021	8:07:00AM	B001	Bills	Done
Aug, 1 2021	8:10:00AM	B002	Bills	New Entry
Aug, 1 2021	8:15:00AM	B002	Bills	Serving	5mins
Aug, 1 2021	8:20:00AM	B002	Bills	Done
Aug, 1 2021	8:30:00AM	C001	Cargo	New Entry
Aug, 1 2021	8:32:00AM	C001	Cargo	Serving	8mins
Aug, 1 2021	9:40:00AM	C001	Cargo	Done

Thank you in advance.

[v-xiaotang]

Hi @ljmanayon 

with measure, you can try this

MinutesTest1 = 
var _b=CALCULATE(MIN('Table'[Time]),FILTER(ALLEXCEPT('Table','Table'[Service ID]),'Table'[Remarks]="Done"))
return IF(MIN('Table'[Remarks])="Serving",DATEDIFF(MIN('Table'[Time]),_b,MINUTE))

or

MinutesTest2 = 
var _b=CALCULATE(MIN('Table'[Time]),FILTER(ALLEXCEPT('Table','Table'[Service ID]),'Table'[Remarks]="Done"))
return IF(MIN('Table'[Remarks])="Serving",FORMAT(MIN('Table'[Time])-_b,"hh:nn:ss"))

result

Best Regards,

Community Support Team _Tang

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

[ljmanayon]

Thanks for your patience, but still it gives me a negative results. 

[v-xiaotang]

Hi @ljmanayon 

not sure but check the order in your measure,  the first parameter is StartDate, the second parameter is enddate.

in my side, there‘s no negative result. if problem still not solved, please share your file and I'll check it later.

Looking forward to receiving your reply.

Best Regards,

Community Support Team _Tang

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

[ljmanayon]

I did follow your solution, but I still get a negative results. 

Here's the sample data that I have. Thanks again for your patience.

https://docs.google.com/spreadsheets/d/1KDoTj5PgzmsFzfY61v8RGk3QMbwXJb0t/edit?usp=sharing&ouid=11128... 

[v-xiaotang · Accepted solution]

Hi @ljmanayon 

I've checked the file, you can try this

Result I want Test = 
    var _endtime=CALCULATE(MIN(test[Time]),FILTER(ALLEXCEPT('test',test[Branch],test[Date/Time],'test'[Service ID]),'test'[Remarks]="Done"))
    var _diff=DATEDIFF(MIN('test'[Time]),_endtime,MINUTE)
return IF(MIN('test'[Remarks])="Serving",_diff&"mins")

result

besides, in your previous example, the format of Time is xx:xx:00, while in your file, the format is xx:xx:xx, so I didn't add seconds into Measure.

Best Regards,

Community Support Team _Tang

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

[ljmanayon]

Thank you for time and patience. Appreciate it a lot.

I'll try your solution as soon as I get home. 

[v-xiaotang]

Hi @ljmanayon 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate!
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,
Community Support Team _Tang

[ljmanayon]

Thanks for your help @v-xiaotang . I tried your solution but the measure gives me a negative results e.g. -60, -38, etc., and the calculated column gives no result.

My bad probably, I forgot to fill-in more data on my sample table. This the actual table looks like.

Branch	Date	Time	Service ID	Product	Remarks	Result I want
Branch A	8/13/21	8:00:00AM	B001	Bills	New Entry
Branch A	8/13/21	8:10:00AM	B001	Bills	Serving	5mins
Branch A	8/13/21	8:15:00AM	B001	Bills	Done
Branch A	8/13/21	8:16:00AM	C001	Cargo	New Entry
Branch A	8/13/21	8:20:00AM	C001	Cargo	Serving	10mins
Branch A	8/13/21	8:30:00AM	C001	Cargo	Done
Branch A	8/13/21	8:35:00AM	B002	Bills	New Entry
Branch A	8/13/21	8:40:00AM	B002	Bills	Serving	20mins
Branch A	8/13/21	9:00:00AM	B002	Bills	Done

[v-xiaotang]

Hi @ljmanayon 

try this 

Result I want = 
    var _b=CALCULATE(MIN('TableNew'[Time]),FILTER(ALLEXCEPT('TableNew','Tablenew'[Service ID]),'TableNew'[Remarks]="Done"))
    var _diff=DATEDIFF(MIN('TableNew'[Time]),_b,MINUTE)
return IF(MIN('TableNew'[Remarks])="Serving",_diff&"mins")

result

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@ljmanayon make sure you have datetime column if not create one like

Date Time = [Date] + [Time]

New column =
var _max = maxx(filter(Table, [Service ID] = earlier([Service ID]) && [Remarks] = " New Entry"),[Date Time])
return
if( [Remarks] = "Serving", datediff([Date Time], _max,minute), blank())

if needed add date to it

New column =
var _max = maxx(filter(Table, [Service ID] = earlier([Service ID]) && [Date] = earlier([Date])  && [Remarks] = " New Entry"),[Date Time])
return
if( [Remarks] = "Serving", datediff([Date Time], _max,minute), blank())

[ljmanayon]

@amitchandak Thanks for answering. However, it returns thousands e.g. 5878, 6485, etc.

[amitchandak]

@ljmanayon , Oh need one correction, My mistake

New column =
var _max = maxx(filter(Table, [Service ID] = earlier([Service ID]) &&  [Date Time] < earlier([Date Time]) && [Remarks] = " New Entry"),[Date Time])
return
if( [Remarks] = "Serving", datediff(_max,[Date Time], minute), blank())

[ljmanayon]

Thanks your help but I got 0 results or blank. And the formula takes so much time to calculate, unfortunately.