Calculate Difference between 2 rows based on another column filter

Hi,

Write these measures

D = CALCULATE(MIN(Data[timestamp]),Data[descript]="Dispatched")

A = CALCULATE(MIN(Data[timestamp]),Data[descript]="Arrived")

Response time = [A]-[D]

Hope this helps.

Hi,

Instead of 2 values, I have 4 values now, can you please tell me how to modify the above column measure?

Ashish_Mathur 

Hi,

This calculated column formula works

Column = if(isblank(CALCULATE(MAX(Data[DateTime]),FILTER(Data,Data[SerialId]=EARLIER(Data[SerialId])&&Data[DateTime]<EARLIER(Data[DateTime])))),blank(),abs(Data[GenHours]-lookupvalue(Data[GenHours],Data[DateTime],CALCULATE(MAX(Data[DateTime]),FILTER(Data,Data[SerialId]=EARLIER(Data[SerialId])&&Data[DateTime]<EARLIER(Data[DateTime]))),Data[SerialId],Data[SerialId])))

Hope this helps.

I am trying to do something similar, but different.  

For a given call (inci_id) AND a given unit (unitcode), I am trying to calculate the time difference between dispatch events (transtype).  The problem is that these values are contained within different rows and not consecutive.  I have attached a sample of my dataset.

Here is the file: https://drive.google.com/file/d/1KNGslhWB4YsFuRbjGOesiLmfegxLOqEE/view?usp=sharing

For example - for inci_id 2019365004 and unit R97 - I want to know the time difference between Dispatched and Arrived.

Can you suggest a solution?

Thanks in advance

Darrel Donatto

Fire Chief, Palm Beach Fire Rescue

Hi,

Show the expected result very clearly.

When I paste in a table it does not post well.

Thank you so much...  This works well when used in a Matrix that groups by incident id and unit.  I modified it slightly to get the format I needed:

Response time = If([A]=0," ", FORMAT([A]-[D],"h:mm:ss"))

Is there a way to get the Maximum time to show in the incident id part of the matrix instead of the minimum?

 

You are welcome.  I do not understand your requirement.  Please show the expected result very clearly.

I am looking to get the maximum unit response time to appear on the line for the incident ID.  Currently, I am getting the minimum time for all incident ids.  For this case, instead of the 6:50 it should show 13:57.

Hi,

Try this measure

Response time_new = MAXX(Values(Data[Inci_id]),[Response time])

Hope this helps.

That is still giving me the least or minimum time of all units for that Incident under the Incident ID row.  I am trying to get the maximum time.

Hi,

Share the link from where i can download your PBI file.