Forum Discussion

axk180022's avatar
axk180022
Helper II
4 years ago
Solved

Calculate Difference between 2 rows based on another column filter

  I would want to find the difference in the GenHours for each serialid. Currently this is how my table looks on power BI. I have filtered the latest and 2nd latest date for each serialid.   ...
Data Protection
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    This calculated column formula works

    =if(CALCULATE(MAX(Data[Dates]),FILTER(Data,Data[serialid]=EARLIER(Data[serialid])))=Data[Dates],ABS(Data[GenHours]-LOOKUPVALUE(Data[GenHours],Data[Dates],CALCULATE(max(Data[Dates]),FILTER(Data,Data[serialid]=EARLIER(Data[serialid])&&Data[Dates]<EARLIER(Data[Dates]))),Data[serialid],Data[serialid])),BLANK())

    Hope this helps.

DarrelDonatto's avatar
DarrelDonatto
Frequent Visitor
4 years ago

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.

closecodeconsoledescriptinci_idradorevtimeinsecstimestamptranstypeunitcodeuserid
 CONSOLE2Dispatched2019365004R159812/31/2019 0:26DR97BMCGARY
 CONSOLE4En-Route2019365004R166612/31/2019 0:27ER97ASABATINO
 CONSOLE2Dispatched2019365007R205012/31/2019 0:34DLD97BMCGARY
 CONSOLE2Arrived2019365004R209212/31/2019 0:34AR97BMCGARY
 CONSOLE2En-Route2019365007R215912/31/2019 0:35ELD97BMCGARY
 CONSOLE2Arrived2019365007R237812/31/2019 0:39ALD97BMCGARY
 CONSOLE2Transport2019365004R287712/31/2019 0:47TR97BMCGARY
FIRCONSOLE2Cleared2019365007R302512/31/2019 0:50CLD97BMCGARY
 CONSOLE2At Hospital2019365004R355012/31/2019 0:59HR97BMCGARY
 CONSOLE2Dispatched2019365017R387012/31/2019 1:04DR99BMCGARY
 CONSOLE2En-Route2019365017R394012/31/2019 1:05ER99BMCGARY
 CONSOLE2Arrived2019365017R433612/31/2019 1:12AR99BMCGARY
FIRCONSOLE2Cleared2019365004R461112/31/2019 1:16CR97BMCGARY

 

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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Show the expected result very clearly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

  • DarrelDonatto's avatar
    DarrelDonatto
    Frequent Visitor
    4 years ago
    Data    
    descriptinci_idtimestampunitcode 
    Dispatched201936500412/31/2019 00:26:00R97 
    En-Route201936500412/31/2019 00:27:00R97 
    Dispatched201936500712/31/2019 00:34:00LD97 
    Arrived201936500412/31/2019 00:34:00R97 
    En-Route201936500712/31/2019 00:35:00LD97 
    Arrived201936500712/31/2019 00:39:00LD97 
    Transport201936500412/31/2019 00:47:00R97 
    Cleared201936500712/31/2019 00:50:00LD97 
    At Hospital201936500412/31/2019 00:59:00R97 
    Dispatched201936501712/31/2019 01:04:00R99 
    En-Route201936501712/31/2019 01:05:00R99 
    Arrived201936501712/31/2019 01:12:00R99 
    Cleared201936500412/31/2019 01:16:00R97 
         
    Expected Result    
    inci_idunitcodeDispatchedArrivedResponse Time
    2019365004R9712/31/2019 00:26:0012/31/2019 00:34:0008:00
        Arrived Time - Dispatch Time
  • DarrelDonatto's avatar
    DarrelDonatto
    Frequent Visitor
    4 years ago

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

     

     

  • DarrelDonatto's avatar
    DarrelDonatto
    Frequent Visitor
    4 years ago

    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?

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

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

  • DarrelDonatto's avatar
    DarrelDonatto
    Frequent Visitor
    4 years ago

    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.

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Try this measure

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

    Hope this helps.

  • DarrelDonatto's avatar
    DarrelDonatto
    Frequent Visitor
    4 years ago

    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.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

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