cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dbravo
Frequent Visitor

Calculate time difference with other rows

Hi,

 

I have a case tracking system that stores the date when a case passes from a state to another in a single row, then I want to calculate the duration time between state changes.

 

I have data like these:

 

id	case_id	date	state_id
25413857	802893	2017-06-01 00:00:02	1
25413858	802937	2017-06-01 00:00:02	1
25413859	889015	2017-06-01 00:00:08	1
25413860	889015	2017-06-01 00:00:57	2
25413861	889030	2017-06-01 00:01:43	1
25413862	889375	2017-06-01 00:01:59	1
25413863	889375	2017-06-01 00:02:00	2
25413864	889030	2017-06-01 00:02:18	2
25413865	889030	2017-06-01 00:02:19	3
25413866	889031	2017-06-01 00:02:36	1
25413867	889031	2017-06-01 00:03:20	2
25413868	889031	2017-06-01 00:03:21	3
25413869	889376	2017-06-01 00:03:23	1
25413870	889376	2017-06-01 00:03:24	2
25413871	889377	2017-06-01 00:03:46	1
25413872	889377	2017-06-01 00:03:47	2
25413873	889375	2017-06-01 00:03:50	3
25413874	889375	2017-06-01 00:03:52	4
25413875	889375	2017-06-01 00:04:02	5
25413876	889375	2017-06-01 00:04:03	6
25413877	889376	2017-06-01 00:04:54	3
25413878	889376	2017-06-01 00:04:55	4
25413879	889378	2017-06-01 00:05:08	1
25413880	889378	2017-06-01 00:05:09	2
25413881	889379	2017-06-01 00:05:21	1
25413882	889343	2017-06-01 00:05:27	1
25413883	889376	2017-06-01 00:05:27	5
25413884	889376	2017-06-01 00:05:32	6
25413885	802867	2017-06-01 00:05:38	1
25413886	889346	2017-06-01 00:05:42	1
25413887	889376	2017-06-01 00:05:43	7
25413888	889379	2017-06-01 00:05:51	2
25413889	888432	2017-06-01 00:06:02	1
25413890	889376	2017-06-01 00:06:07	8
25413891	889376	2017-06-01 00:06:08	9
25413892	888432	2017-06-01 00:06:09	2
25413893	889154	2017-06-01 00:06:31	1
25413894	889154	2017-06-01 00:06:32	2
25413895	889379	2017-06-01 00:06:33	3
25413896	889381	2017-06-01 00:06:38	1
25413897	889381	2017-06-01 00:06:39	2
25413898	889154	2017-06-01 00:06:40	3
25413899	889377	2017-06-01 00:06:46	3
25413900	889377	2017-06-01 00:06:48	4
25413901	889154	2017-06-01 00:06:52	4
25413902	889154	2017-06-01 00:06:53	5
25413903	889377	2017-06-01 00:06:55	5
25413904	889377	2017-06-01 00:06:56	6
25413905	889378	2017-06-01 00:07:29	3
25413906	889378	2017-06-01 00:07:30	4

 

I'm lost, I've tried using some DAX functions but I can't solve this, I hope you can help me.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Calculate time difference with other rows

My video here should help you. https://m.youtube.com/watch?v=xN2IRXQ2CvI



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

1 REPLY 1

Re: Calculate time difference with other rows

My video here should help you. https://m.youtube.com/watch?v=xN2IRXQ2CvI



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors