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 axk180022,

See Formula Below, combined logic into single column and set value to blank when not the max date per serial number

Difference = 
var _serialid = [serialid]
var _date = [Dates]
return

if('Table'[Dates] = CALCULATE(MAX('Table'[Dates]), filter(ALL('Table'),'Table'[serialid] = _serialid)),
abs('Table'[GenHours] - 
    CALCULATE(MAX('Table'[GenHours]), 
        TOPN(1, FILTER('Table', [serialid] = _serialid && [Dates]<_date), 'Table'[Dates], DESC))
    )
    , BLANK())

Thank you so much, this solution worked, but is it possible to make the other values to be left blank?   like below

richbenmintz 

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.