Calculate Difference between 2 rows based on another column filter

[axk180022]

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.

Now i would want to find the difference between the GenHours that is being diplayed on Power BI.

Can someone please help.

[Syndicate_Admin]

This sent me to my analysis.

Thank you!

[Syndicate_Admin]

This sent me to my analysis.

Thank you!

[richbenmintz]

Hi @axk180022,

Are you able to provide the data as a table as opposed to an image and the expected outcome. Not sure I understand the requirement based on your post.

Thanks,

[axk180022]

The one highlighted is the output I require, Which is the difference of GenHours between 2 latest dates. 

@richbenmintz 

[richbenmintz]

Hi @axk180022 ,

could you provide the data?

[axk180022]

Since Im a new user im unable to upload the above data. is there a link you can provide me?

@richbenmintz 

[richbenmintz]

Hi @axk180022 ,

You can paste the data in a table, or share your pbix through google drive or onedrive

[axk180022]

serialid	GenHours	Dates
cvs-001	300	12/1/2019
cvs-001	200	1/2/2020
cvs-001	200	2/2/2020
cvs-002	800	4/3/2020
cvs-002	300	5/3/2020
cvs-002	400	6/3/2020
cvs-003	250	5/5/2020
cvs-004	820	5/2/2020
cvs-004	350	6/6/2020
cvs-004	150	6/7/2020

Here is the table @richbenmintz 

[richbenmintz]

Hi @axk180022 

If you create a calc column that get the prior date value like

Prior Date Value = 
var _serialid = [serialid]
var _date = [Dates]
return
CALCULATE(MAX('Table'[GenHours]), TOPN(1, FILTER('Table', [serialid] = _serialid && [Dates]<_date), 'Table'[Dates], DESC))

you can create another column that derives the difference

diff = [GenHours] - [Prior Date Value] 

hope this helps

[richbenmintz]

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())

[axk180022]

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

serialid	GenHours	Dates	Difference
cvs-001	300	12/1/2019
cvs-001	200	1/2/2020
cvs-001	200	2/2/2020	0
cvs-002	800	4/3/2020
cvs-002	300	5/3/2020
cvs-002	400	6/3/2020	100
cvs-003	250	5/5/2020	250
cvs-004	820	5/2/2020
cvs-004	350	6/6/2020
cvs-004	150	6/7/2020	200

@richbenmintz 

[Ashish_Mathur · Accepted solution]

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]

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.

closecode	console	descript	inci_id	radorev	timeinsecs	timestamp	transtype	unitcode	userid
	CONSOLE2	Dispatched	2019365004	R	1598	12/31/2019 0:26	D	R97	BMCGARY
	CONSOLE4	En-Route	2019365004	R	1666	12/31/2019 0:27	E	R97	ASABATINO
	CONSOLE2	Dispatched	2019365007	R	2050	12/31/2019 0:34	D	LD97	BMCGARY
	CONSOLE2	Arrived	2019365004	R	2092	12/31/2019 0:34	A	R97	BMCGARY
	CONSOLE2	En-Route	2019365007	R	2159	12/31/2019 0:35	E	LD97	BMCGARY
	CONSOLE2	Arrived	2019365007	R	2378	12/31/2019 0:39	A	LD97	BMCGARY
	CONSOLE2	Transport	2019365004	R	2877	12/31/2019 0:47	T	R97	BMCGARY
FIR	CONSOLE2	Cleared	2019365007	R	3025	12/31/2019 0:50	C	LD97	BMCGARY
	CONSOLE2	At Hospital	2019365004	R	3550	12/31/2019 0:59	H	R97	BMCGARY
	CONSOLE2	Dispatched	2019365017	R	3870	12/31/2019 1:04	D	R99	BMCGARY
	CONSOLE2	En-Route	2019365017	R	3940	12/31/2019 1:05	E	R99	BMCGARY
	CONSOLE2	Arrived	2019365017	R	4336	12/31/2019 1:12	A	R99	BMCGARY
FIR	CONSOLE2	Cleared	2019365004	R	4611	12/31/2019 1:16	C	R97	BMCGARY

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]

Hi,

Show the expected result very clearly.

[DarrelDonatto]

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

[DarrelDonatto]

Data
descript	inci_id	timestamp	unitcode
Dispatched	2019365004	12/31/2019 00:26:00	R97
En-Route	2019365004	12/31/2019 00:27:00	R97
Dispatched	2019365007	12/31/2019 00:34:00	LD97
Arrived	2019365004	12/31/2019 00:34:00	R97
En-Route	2019365007	12/31/2019 00:35:00	LD97
Arrived	2019365007	12/31/2019 00:39:00	LD97
Transport	2019365004	12/31/2019 00:47:00	R97
Cleared	2019365007	12/31/2019 00:50:00	LD97
At Hospital	2019365004	12/31/2019 00:59:00	R97
Dispatched	2019365017	12/31/2019 01:04:00	R99
En-Route	2019365017	12/31/2019 01:05:00	R99
Arrived	2019365017	12/31/2019 01:12:00	R99
Cleared	2019365004	12/31/2019 01:16:00	R97
Expected Result
inci_id	unitcode	Dispatched	Arrived	Response Time
2019365004	R97	12/31/2019 00:26:00	12/31/2019 00:34:00	08:00
				Arrived Time - Dispatch Time

[Ashish_Mathur]

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]

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]

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

[DarrelDonatto]

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.