Adding new column based on difference of rows

[Anonymous]

Hi,

I am new to Power BI and struggling with something which otherwise must be simple I believe. I would like to have a difference of IN and OUT timings for a particular license for different days. This would basically tell the total time spent by the user with the license on a given day. 

I might explain it better with an example so here it goes:

The original source table:

time	source	direction	type	username	machine	date
10:21:36 AM	ARCGIS	IN	ARC/INFO	vp	dw220	4/8/2019
10:21:36 AM	ARCGIS	IN	Grid	vp	dw220	4/8/2019
10:22:06 AM	ARCGIS	OUT	ARC/INFO	vp	dw220	4/8/2019
10:22:14 AM	ARCGIS	OUT	Grid	vp	dw220	4/8/2019
11:21:36 AM	ARCGIS	IN	ARC/INFO	vp	dw220	4/9/2019
11:28:06 AM	ARCGIS	OUT	ARC/INFO	vp	dw220	4/9/2019

The table post adding a new column:

time	source	direction	type	username	machine	date	Time Spent
10:21:36 AM	ARCGIS	IN	ARC/INFO	vp	dw220	4/8/2019	0:00:30
10:21:36 AM	ARCGIS	IN	Grid	vp	dw220	4/8/2019	0:00:38
10:22:06 AM	ARCGIS	OUT	ARC/INFO	vp	dw220	4/8/2019	NULL
10:22:14 AM	ARCGIS	OUT	Grid	vp	dw220	4/8/2019	NULL
11:21:36 AM	ARCGIS	IN	ARC/INFO	vp	dw220	4/9/2019	0:06:30
11:28:06 AM	ARCGIS	OUT	ARC/INFO	vp	dw220	4/9/2019	NULL

Here the first row of the new column is: (Time of OUT direction for ARC/INFO license on 4/8/2019) - (Time of IN direction for ARC/INFO license on 4/8/2019) 

Similarly, the second row of the new column is: (Time of OUT direction for Grid license on 4/8/2019) - (Time of IN direction for Grid license on 4/8/2019) 

the third row of the new column is: (Time of OUT direction for ARC/INFO license on 4/9/2019) - (Time of IN direction for ARC/INFO license on 4/9/2019) 

[PattemManohar]

@Anonymous  Please add a New Column as below

TimeDiff = 
VAR _LkpOut = LOOKUPVALUE(Test303LkpTimeDiff[Time],Test303LkpTimeDiff[Type],Test303LkpTimeDiff[Type],Test303LkpTimeDiff[Direction],"OUT",Test303LkpTimeDiff[Date],Test303LkpTimeDiff[Date])
RETURN IF(Test303LkpTimeDiff[Direction]="IN",_LkpOut-Test303LkpTimeDiff[Time],BLANK())

[Anonymous]

Gives an error: 

I would like to add that there are multiple data in the sheet and not just ones I mentioned in the sample. Probably that could be causing the issue. I tried to merge the date and time column and named it 'DateTime' to apply the below DAX:

Lav_Inv_Score1 = 
VAR myID = 'LM_debuglog'[type]
VAR myLastDate = MAXX(FILTER('LM_debuglog','LM_debuglog'[type]=myID && 'LM_debuglog'[DateTime] < EARLIER('LM_debuglog'[DateTime])),'LM_debuglog'[DateTime])
RETURN SUMX(FILTER('LM_debuglog','LM_debuglog'[DateTime] = myLastDate && 'LM_debuglog'[type] = myID),'LM_debuglog'[DateTime])

However the above gives me an error. Any suggestions?

[PattemManohar · Accepted solution]

@Anonymous  In that case, add a Rank field and will use this rank field in the logic instead of date

Rnk = RANKX(FILTER(Test303LkpTimeDiff,[Type]=EARLIER(Test303LkpTimeDiff[Type])),[Date]+[Time],,ASC) 

Then, the updated logic will be

TimeDiff = 
VAR _LkpOut = LOOKUPVALUE(Test303LkpTimeDiff[Time],Test303LkpTimeDiff[Type],Test303LkpTimeDiff[Type],Test303LkpTimeDiff[Direction],"OUT",[Rnk],[Rnk]+1)
RETURN IF(Test303LkpTimeDiff[Direction]="IN",_LkpOut-Test303LkpTimeDiff[Time],BLANK())