Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
dnewton
Helper II
Helper II

Calculate difference in minutes between rows (example included)

Hi,

 

I'm trying to create a column or measure to calculate the amount of time agents have spent in "withdrawn".

 

In the below example you can see my data - Untitled.png

 

Starting from the bottom of the table you can see the agent has gone into state e_state "Agent Withdrawn" at e_UTC "10/12/2017 06:18:55" and then went into e_state "Agent Assigned" at e_UTC 10/12/2017 06:30:35.

 

How can I automatically calculate how much time was spent in "Agent Withdrawn"? So the time between Agent Withdrawn and Agent Assigned i.e. 10/12/2017 06:18:55 to 10/12/2017 06:30:35 = 11 minutes and 40 seconds.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.