Forum Discussion
Calculate difference in minutes between rows (example included)
The calculation goes something along these lines (I developed this is for a different use case but same problem):
c_Duration = VAR next = MINX( FILTER(Table, Table[AgentName]=EARLIER(Table[AgentName]) && Table[Time]>EARLIER(Table[Time]) ), Table[Time]) RETURN IF(ISBLANK(next),DATEDIFF([Time],NOW(),SECOND),DATEDIFF([Time],next,SECOND))
This is a calculated column in your table that will return the Duration in seconds between one row and the next row. So basically what is happening is that you are creating a table that filters down to the same Agent as the current row and all Times that are after the current row. Get the earliest date of that table (MINX). If there is a blank, you are at the top row in the dataset so get the Duration between that time an NOW().
Please note that this is using MINX so using this on a dataset that contains like 11 million rows is a bad idea but it works great for smaller tables.
The calculation goes something along these lines (I developed this is for a different use case but same problem):
c_Duration = VAR next = MINX( FILTER(Table, Table[AgentName]=EARLIER(Table[AgentName]) && Table[Time]>EARLIER(Table[Time]) ), Table[Time]) RETURN IF(ISBLANK(next),DATEDIFF([Time],NOW(),SECOND),DATEDIFF([Time],next,SECOND))
This is a calculated column in your table that will return the Duration in seconds between one row and the next row. So basically what is happening is that you are creating a table that filters down to the same Agent as the current row and all Times that are after the current row. Get the earliest date of that table (MINX). If there is a blank, you are at the top row in the dataset so get the Duration between that time an NOW().
Please note that this is using MINX so using this on a dataset that contains like 11 million rows is a bad idea but it works great for smaller tables.
This is amazing, I have been trying on and off for days to get a calculation and this seems to be working!
I suppose the next question is I now have a column full of total seconds, I need to get that to display in mins.
I guess [c_Duration]/60 would be too easy!
Yep, that's how I would do it. Or DIVIDE([c_Duration],60) but same thing! Or, just change SECOND in your DATEDIFFS to MINUTE.