Forum Discussion
Calculating Time Difference
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.
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.
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.
