Splitting table rows from value

[jagostinhoCT]

Hello,

I need to calculate the duration between Events for each particular user working in a specific file in a datasource that contains info from multiple users. Sample table below.

The goal is to get the Duration column to calculate the duration for the next user action in the same FileName.

A manual calculation in excel would be something like the following image.

So, basically, I want to subtract the next datetime row for the same user and filename to the current row.

If such next row does not exist then set the duration to 0 (as for the FileClose at the end, for instance).

Thank you for your help.

DateTime	Username	FileName	EventName	ViewName	Duration
18/02/2020 14:00:00	Ann	A	FileOpen	-	00:00:00
18/02/2020 14:10:00	Ann	A	ViewActivated	Level 2 - WRK	01:00:00
18/02/2020 14:20:00	Ann	B	FileOpen	-	00:00:00
18/02/2020 14:30:00	Ann	B	ViewActivated	Level 3	00:50:00
18/02/2020 14:40:00	Ben	A	FileOpen	-	00:00:00
18/02/2020 14:50:00	Ben	A	ViewActivated	Level 2 - WRK	00:10:00
18/02/2020 15:00:00	Ben	A	ViewActivated	Legend	00:30:00
18/02/2020 15:10:00	Ann	A	Idle	-	00:30:00
18/02/2020 15:20:00	Ann	B	FileClose	-	00:00:00
18/02/2020 15:30:00	Ben	A	FileClose	-	00:00:00
18/02/2020 15:40:00	Ann	A	FileClose		00:00:00

[ChrisMendoza]

@jagostinhoCT -

You can do in Power Query as well:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSN7TQNzIwMlAwNLEyMAAiJR0lx7w8EAnEbpk5qf4FqSCuLhAbQJXE6qBrNcTQGpaZWu6YXJJZlliSmgLk+6SWpeYoGCnoKoQHeQP5hjjNMkIxy4kUZxhjaMXuDGOwKaY4TDGBmuKUSnI4mGJoJRQOBtCwQzfLFB4d+MxKT81LARtijMMQzIjxTMlJhfsDlzbsceCck1+cSiAMTOGRgBp8xOk1wZoEYXqRtcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTime = _t, Username = _t, FileName = _t, EventName = _t, ViewName = _t, Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type text}, {"Username", type text}, {"FileName", type text}, {"EventName", type text}, {"ViewName", type text}, {"Duration", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Username", "FileName"}, {{"Grouped", each _, type table [DateTime=text, Username=text, FileName=text, EventName=text, ViewName=text, Duration=time]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndex", each Table.AddIndexColumn([Grouped],"Index",1,1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each let tblName = [AddIndex] in Table.AddColumn([AddIndex],"NextDateTime", each tblName{[Index]}[DateTime])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"DateTime", "EventName", "ViewName", "Duration", "NextDateTime"}, {"DateTime", "EventName", "ViewName", "Duration", "NextDateTime"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Custom", {{"NextDateTime", null}}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Grouped", "AddIndex"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"DateTime", type datetime}, {"NextDateTime", type datetime}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "DurationCalculated", each Duration.TotalMinutes([NextDateTime]-[DateTime]))
in
    #"Added Custom2"

[amitchandak]

Try like. Add or remove conditions as per need

Datediff = table[datetime] = minx(filter(table,table[file name] = earlier(table[file name]) && table[user name] = earlier(table[user name])
										 && table[datetime] > earlier(table[datetime])),table[datetime])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[jagostinhoCT]

thank you for such quick reply  , 

I am getting an error of type

"Too few arguments were passed to the MINX function. The minimum argument count for the function is 2."

the actual formula I am using is this

DateDiff = 'view-activated collected'[DateTime] = MINX(FILTER('view-activated collected','view-activated collected'[DocPath] = EARLIER('view-activated collected'[DocPath] && 'view-activated collected'[Username] = EARLIER('view-activated collected'[Username]) && ('view-activated collected'[DateTime] > EARLIER('view-activated collected'[DateTime])),'view-activated collected'[DateTime])))

@amitchandak

[amitchandak · Accepted solution]

Try

DateDiff = 'view-activated collected'[DateTime] - MINX(FILTER('view-activated collected',
					'view-activated collected'[DocPath] = EARLIER('view-activated collected'[DocPath]) 
				&& 'view-activated collected'[Username] = EARLIER('view-activated collected'[Username]) 
				&& 'view-activated collected'[DateTime] > EARLIER('view-activated collected'[DateTime])
				),'view-activated collected'[DateTime])

[jagostinhoCT]

It is working very well, thank you.

Found an unintended consequence of the requirement, though.

Can the last DateDiff calculation for the last entry for user+docpath be set to 0?