Forum Discussion

ljmanayon's avatar
ljmanayon
Frequent Visitor
5 years ago
Solved

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...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

v-xiaotang's avatar
v-xiaotang
Community Support
5 years ago

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.