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.
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.