Forum Discussion
Calculating Time Difference
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.
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.
13 Replies
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())amitchandak Thanks for answering. However, it returns thousands e.g. 5878, 6485, etc.
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())
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.
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 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.
Thanks for your patience, but still it gives me a negative results.
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.
